SQL Full-Text Search (FTS) - A Brief Overview
Topic Extracted from Knowledge Xpert for SQL Server

SQL Full-Text Search (FTS) allows users to conduct rapid searches against textual content stored in databases. These searches are an order of magnitude faster than if you were using a LIKE statement, and they can be done against content which is stored in its native format (i.e. a Word or PDF document stored in columns of the Image or VarBinary data type). The search is actually conducted against an index (stored in the file system) which contains pointers for each word to the table and rows which contain this word. This index is built asynchronously by an indexing engine called MSFTESQL.

SQL FTS also has rich language features which allow natural language searching in a multitude of languages. This allows you to search on all forms of a word. For example, for a noun like book searches will match with book, books, or book's and for a verb like book searches will match with book, books, booked, booking, etc. The language features also captures language rules for a variety of non Western languages such as Chinese, Japanese, Thai, and Korean.

New Features in SQL FTS 2005

SQL Server 2005 introduced several new features to FTS:

• Significant performance increases especially with indexing.
   
• Secure by default. All iFilters (the component which extracts the text from the content stored in the rows) must be signed before SQL Server FTS will load them.
   
• The ability to full-text index Indexed Views which simplifies partitioning.
   
• Improved Language Support SQL 2005 FTS now supports indexing and searching in 23 different languages. SQL 2005 FTS will respect language tags stored in the content which the iFilter can interpret. You can also override the language settings on a column in your query.
   
• Accent Insensitivity-SQL FTS can now be configured to be accent insensitive so searches on resume will match with résumé. This option is enabled by default, but can be disabled.
   
• Noise Insensitivity-SQL FTS queries will no longer break when one of the search arguments (SARGs) is a noise word. This option is enabled by default, but can be disabled.
   
• Thesaurus support in the Contains and FreeText predicates.
   
• Multi-Column Queries-SQL FTS supports searching a single full-text indexed column, all full-text indexed columns, or a sub-set of the full-text indexed columns in a single query.
   
• Support for linked servers-It is now possible to query full-text catalogs on remote server through a linked server.
   
• Replication support If a table is full-text indexed you can replicate the full-text properties to your subscriber if the subscription database is full-text enabled.
   
• Backup and restored support for full-text catalogs. You can now backup your full-text catalog and restore it on a different server.
   
• Attach/detach full-text catalogs with your databases. You can include your full-text catalog in your detached database files and optionally reattach your full-text catalogs along with your database.
   
• Properties SQL FTS 2005 now supports the indexing and querying of document properties stored in the Image or VarBinary(MAX) data type columns.
   
• Troubleshooting utilities two new utilities ship with SQL 2005 to assist in the troubleshooting efforts:
   
  • lrtest - an executable which allows you to see how the word breakers interpret a token at query time and at index time
     
  • CiDump - a tool which allows you to view the contents of your catalog to determine what is indexed, and how tokens are stored in the index.

  You can find these file in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn
   

• The indexing logs are now stored as plain text in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Log

SQL 2005 FTS is the third generation server service for Microsoft SQL Server, and is by far the most feature rich and offers the best performance.