Understanding SQL Server Full-Text Indexing
By Mike Gunderloy
Microsoft SQL Server supports T-SQL, an implementation of ANSI standard SQL. T-SQL is designed to (among other things) search for matches in your data. For example, if you've created a table with a column named Notes you could construct these querIEs:
SELECT * FROM MyTable WHERE Notes = 'Deliver Tuesday'
SELECT * FROM MyTable WHERE Notes LIKE '%caution%'
But what if you're not looking for an exact match, either to the full text of the column or a part of the column? That's when you need to go beyond the standard SQL predicates and use SQL Server's full-text search capabilitIEs. With full-text searching, you can perform many other types of search:
- Two Words near each other
- Any Word derived from a particular root (for example run, ran, or running)
- Multiple Words with distinct weightings
- A word or phrase close to the search Word or phrase
In this article, I'll show you how to set up and use full-text searching in SQL Server 2000, and give you a sneak peek of the changes that are coming in this area when SQL Server 2005 ships next year.
Full-Text Indexing ArchitectureYou might be a bit surprised to learn that SQL Server doesn't handle its own full-text indexing tasks. Any version of Windows that SQL Server will run on includes an Operating system component named the Microsoft Search Service. This service provides indexing and searching capabilities to a varIEty of applications.
