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
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 servers.
- Full-text catalogs have integrated backup, restore and recovery 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,
Lots of other enhancements too. For more information, see this MSDN article.