Use Keyword Query Syntax instead of SQL Syntax for SharePoint 2010 Search Queries
Posted
Wednesday, December 22, 2010 2:03 PM
by
CoreyRoth
SharePoint 2010 has three syntaxes that you can use to execute search queries, Keyword, SQL, and FAST Query Language (FQL). In SharePoint 2007, we only had the first two of course. You may not know it, but you probably use Keyword Query Syntax every day when you visit your search center. This is your basic keyword search, but in reality it has become extremely powerful now in SharePoint 2010. This wasn’t the case with SharePoint 2007. Keyword Syntax did not support wildcard search, comparison operators, or Boolean operators. This often led people to develop custom code solutions using the FullTextSqlQuery class (or the web service) to use the SQL Syntax which supported these advanced query scenarios.
SharePoint 2010 changed everything though. They added wildcard search, comparison operators, Boolean operators, proximity operators, and synonym operators to the Keyword syntax. All of these things were previously available using SQL Syntax, but now you can do them using Keyword Syntax in SharePoint 2010. Since all of these new features have been added, in my eyes, this effectively (but not officially) makes the SQL Syntax deprecated. I’ve never been a big fan of the SQL Syntax but there were times in 2007 that it was a necessary evil. Unfortunately, when you used it in the CoreResultsWebPart, it prevented you from using a bunch of other features such as best bets, keyword highlighting, RSS, federated search and more. Since the Keyword Syntax supports all of these new operators now, you can get that functionality and not worry about having to lose other desired functionality that you did with Full Text SQL Queries.
You can write really advanced queries with Keyword Syntax. For example:
shirt Color:Red AverageRating >= 4 NOT Department:”Sales”
This would return anything with the word Shirt in it with a color property value of red, rated at least 4 stars and it can’t be from the sales department. As another example:
budget Scope:”Local SharePoint Sites” Scope:”Corporate Web Site” NOT Scope:”File Share”
This would return results matching the word budget from the scopes pointing to the SharePoint site itself as well as the corporate web site, but it would exclude results from the file share. The Keyword syntax has a lot of power now and I think you will be hard pressed to find a query it can’t do.
Another reason to use the Keyword syntax is that both SharePoint Search and FAST Search support it. If you wrote a custom application using SharePoint Search and Keyword queries and later decided to implement FAST, your queries would still work (assuming you indexed the same content). That gives you a ton of code portability.
Of course, if you don’t believe me, take a look at the SDK where they list the following tip.
“The Keyword syntax enhancements in SharePoint Server search provide support for functionality that was previously available only by using SQL syntax, such as comparison, logical, and wildcard operators. If the Keyword syntax meets the requirements for the queries you need to construct for your custom search application, we recommend that you use it instead of the SQL syntax. You should also use the Keyword syntax if you are creating an application that is intended for use with both FAST Search Server 2010 for SharePoint and SharePoint Server search.”
What about FAST? FQL is extremely powerful and you should take advantage of it in your advanced search scenarios. The cool thing about FQL is that it also uses the KeywordQuery class by setting the EnableFQL property to true. This in a lot of ways makes it an extension to the Keyword Query syntax. You can use FQL or Keyword Syntax to execute search queries in FAST. I recommend using Keyword Syntax for most queries but when you need more control of how the query or results are presented, you should switch to FQL.
All of this is just my opinion of course. If you have some overwhelming reason that you need to use SQL Syntax, then by all means continue. However, I recommend to anyone starting out writing search code to try the Keyword Syntax first.