This post originated from an RSS feed registered with .NET Buzz by Raymond Lewallen. Original Post: Changes and enhancements to full text indexing in Sql Server 2005 Feed Title: Raymond Lewallen Feed URL: /error.htm?aspxerrorpath=/blogs/raymond.lewallen/rss.aspx Feed Description: Patterns and Practices, OOP, .Net and Sql Latest .NET Buzz Posts Latest .NET Buzz Posts by Raymond Lewallen Latest Posts From Raymond Lewallen

Sql Server 2005 brings some good enhancements to full-text searching capabilities.  But why would you want to use full-text searches?  Well, it is not always the case where people are given a drop down box of choices to choose from when wanting to perform a search.  Certainly, that scenario is beneficial and efficient, as we are guaranteed to be able to make the most out of our indexing and data structuring techniques when we know the exact data that we are going to be querying.  What is even better is that more times than not, that drop down list is populated from an indexed field in the database somewhere.

What matters is when you are allowing people to enter in their own criteria for returning results, like a free text box.  In this scenario, most people use a LIKE clause, and that works very well.  However, the LIKE clause only works on character patterns.  When dealing with millions of rows, a full-text index can seriously outperform a LIKE clause search using the same search value.  In order to perform a linguistic search with words and phrases, you need to use full-text indexing to get this performance out of your database.  You never know what someone is going to type into a free text box, and full-text indexing, because of its unique indexing of individual pieces of the text, provides an efficient and effective way to quickly return results when searching for free text words and phrases.

Some of the changes and enhancements are:

• Full-text indexing of XML data. Woo hoo!
• You can now run full-text queries against linked server.
• Full-text catalogs have integrated backup, restore and restore capabilities.
• Greatly improved speed of full-text search performance and index building performance.
• Data definition languages statements for creating and altering full-text indexes.  If you were using the system stored procs before, not to fear, they still exist, but have been depreciated.
• Now you can use sp_detach and sp_attach_db to move full-text catalogs.
• You can now include multiple columns in your full-text searches, instead of the one-or-all approach we are familiar with.
• Pre-computed ranking optimizations when using a FREETEXTTABLE query, also with improved ranking algorithms.  No more on-the-fly, costly rankings.

Lots of other enhancements too.  For more information, see this MSDN article.