in

Dot Net Mafia

Group site for developer blogs dealing with (usually) .NET, SharePoint 2013, SharePoint 2010, Office 365, SharePoint Online, and other Microsoft products, as well as some discussion of general programming related concepts.

This Blog

Syndication

Archives

Corey Roth [MVP]

A SharePoint MVP bringing you the latest time saving tips for SharePoint 2013, Office 365 / SharePoint Online and Visual Studio 2013.

Use Keyword Query Syntax instead of SQL Syntax for SharePoint 2010 Search Queries

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.

Comments

 

Twitter Trackbacks for Use Keyword Query Syntax instead of SQL Syntax for SharePoint 2010 Search Queries - Corey Roth [dotnetmafia.com] on Topsy.com said:

Pingback from  Twitter Trackbacks for                 Use Keyword Query Syntax instead of SQL Syntax for SharePoint 2010 Search Queries - Corey Roth         [dotnetmafia.com]        on Topsy.com

December 23, 2010 4:49 PM
 

Use Keyword Query Syntax instead of SQL Syntax for SharePoint 2010 Search Queries said:

Pingback from  Use Keyword Query Syntax instead of SQL Syntax for SharePoint 2010 Search Queries

March 23, 2011 4:23 AM
 

Andy said:

Hi,

Just a quick question. If I enabled the "EnableFQL" flag to true?

Do the FAST synonym search still working?

I just found a case that if I enable the flag to true, the synonym search is not working anymore.

July 11, 2011 3:42 AM
 

CoreyRoth said:

@Andy Hmm it may not, I'd have to check to confirm.

July 12, 2011 1:00 PM
 

Yonathan Masovich said:

If the EnableFQL = true; the Keyword query complains on Malformed query error - in my case it didn't work, but, maybe it depends on complicity of your queries.

I do know, that FQL queries have very certain syntax.

July 14, 2011 3:52 AM
 

kami said:

Does SharePoint Search support OR operand for keyword text and property restrictor combination? example:

microsoft OR title:sharepoint

I got query is malformed error message.

It works with AND operator.

It also works if both are property restrictions, ie propertyA OR propertyB

August 18, 2011 3:28 AM
 

CoreyRoth said:

@Kami you're right.  That syntax does not appear to be sorted.  I've tried several different ways that I thought the query might go through and none worked.

August 30, 2011 10:48 AM
 

OnLearningCsharp said:

I am sorry for my poor English.But I have a question about how to limit the SEARCH SCOPE.I am trying to query the FAST Search Server which has crawled a Database File.I plan to crawl the database several times and store the result in different "Collection".Because I can not fill the information into a single table.I want the url will be different when necessary.

Now I find it difficault to explain it to you with English.And maybe it is difficault for you to understand me,too.

However,if you can tell me how to use SEARCH SCOPE,it will be a GREAT HELP.

Thank you very much!

December 26, 2011 9:37 PM
 

Search in SharePoint 2013 Preview « Rajesh SharePoint Corner said:

Pingback from  Search in SharePoint 2013 Preview « Rajesh SharePoint Corner

January 7, 2013 2:47 AM
 

Keyword search with SQL Server | Search RounD said:

Pingback from  Keyword search with SQL Server | Search RounD

March 9, 2014 11:12 PM

Leave a Comment

(required)  
(optional)
(required)  
Add

About CoreyRoth

Corey Roth is an independent SharePoint consultant specializing in ECM, Apps, and Search.
2012 dotnetmafia.
Powered by Community Server (Non-Commercial Edition), by Telligent Systems