by dotnetpete
8. September 2008 21:26
Greg Low did an amazing talk at Tech.Ed about full text seach (FTS) in SQL2008. I hadn't had much exposure to FTS so it was good to see what it had to offer. I thought it was just an efficient way to do a like search in a text field. It's so much more.
For example, FTS out of the box can index may common file formats i.e. doc, xls. You can get the full list of supported document types by running the following query:
select document_type, path from sys.fulltext_document_types
Here's a detailed article on how to setup a FTS catalog. One this that is not all that clear is that on the "Select Table Columns" step, if your FTS column is a data type of image you are required to select another column in the table the holds the document type of the document in the image column. This is how FTS knows which filter to use to index the data.
http://blog.sqlauthority.com/2008/09/05/sql-server-creating-full-text-catalog-and-index/
I found an excellent article that explains the capabilities of FTS. It's SQL2005 specific, however everything (to my knowledge is supported in SQL2008).
http://www.developer.com/db/article.php/3446891
Things to investigate:
- How to install and activate other predefined filters (PDF not supported by default),
- For the hard core, how to write your own filter extension.
Also here's the full article on MSDN which explains FTS in SQL2008 in depth:
Greg Low also has an interesting article on using the sys.dm_fts_parser DMF to test how a string is interpreted by the FTS parser.
Subscribe