Full-Text Indexing Workbench

Robert Sheldon continues his guide to Full-Text Indexing, by putting down his pen and reaching for SSMS for a practical workbench on Full-Text indexing. There is nothing like trying things out to make ideas click.

After you set up a full-text index on a table in a SQL Server 2005 or SQL Server 2008 database, you can perform a full-text search on the indexed columns in the table. To perform a full-text search, you can use the CONTAINS predicate or the FREETEXT predicate in your query’s WHERE clause.

This workbench provides you with examples of how to use these predicates to perform a full-text search. (Note that SQL Server also supports two full-text functions, CONTAINSTABLE and FREETEXT table, but this workbench focuses only on the predicates.)

When you include the CONTAINS or FREETEXT predicate in your WHERE clause, the query engine searches the columns that are specified in the predicate arguments. These columns must be included in the full-text index that is defined on the specified table. The predicates also let you make use ofthe thesaurus that is available for any of the supported languages.

If you’re new to full-text indexes and searches, you should first review the Simple-Talk article “Understanding Full-Text Indexing in SQL Server,” published December 29, 2008. The article describes how full-text indexes are implemented in SQL Server 2005 and 2008, and provides examples of how to create those indexes.

To run the examples in this workbench, you should first set up the necessary environment to test the full-text queries. The following T-SQL statements create the StormyWeather table, populate the table, create the ftcStormyWeather full-text catalog, and then create a full text index on the table. The index is added to the ftcStormyWeather catalog.

That’s all there is to using the CONTAINS and FREETEXT predicates. Keep in mind that CONTAINS is more precise than FREETEXT. And, of course, a full-text search can be much more complex than shown in the examples here. Be sure to check out SQL Server Books Online for more details about both of these predicates.