FullTextSQLQuery and the Like statement in SharePoint 2010

At my current client, we encountered an issue with some of the custom search web parts. In these web parts, we would perform a custom FullTextSQLQuery against the search index using the Like statement against custom created managed properties. Somehow, the search did not return any results, no matter what the keyword was.

This behavior was kind of strange and indicated that there must be something wrong with the creation of the managed properties, or the population of these properties. Several steps were taken, like again executing a full crawl, remapping the managed properties and so on.

Still, the search did not return any results. This drove me a little bit nuts until I found a post in the social MSDN by Steve Curran. It turns out, Microsoft changed the behavior a little bit since SharePoint 2010. Managed properties on SharePoint 2010 have an additional setting called ‘Reduce storage requirements for text properties by using a hash for comparison’ that influence the way the query behaves when using the Like statement (or the contains statement for that matter). If not checked, any Like comparison against this managed property will always return null, no matter what you put in. Checked this setting, ran a full crawl again and search was working fine.

So thank you Steve!