Hopefully everyone had some fun over the weekend with their digital cameras and listening to some good photo podcasts. Today we’ll return to the subject of Full Text Searching (FTS) under SQL Server 2005.
If you’re like me one of your questions is “OK, FTS is great, but exactly what data can I index with it?” Good question, a quck reference of the books online gives us the answer.
Char, nchar, varchar, nvarchar, text, ntext, xml, varbinary(max), and image are the valid data types for FTS. However, according to the documentation text, ntext, and image are going to be removed in a future version of SQL Server, so I’m going to avoid them and so should you.
Char, nchar, varchar, nvarchar are pretty straight forward, and probably expected, so I won’t go into them further. XML is pretty cool, as you may be aware with SQL Server 2005 Microsoft added the ability to store XML in the database. You can then do XML queries into these XML fields, a subject for a future post. However, MS also allows you to search inside these XML fields using Full Text Searching as well.
The final one that needs explanation then is varbinary(max). I’m sure you’re scratching you’re head over this, how can FTS work on binary data? Well remember, the Full Text Search engine is built on top of the Microsoft Indexing Service, which can scan inside Word, Excel, etc files stores on a server.
Thus FTS can actually look inside files stored inside varbinary(max) and if they have the proper extension FTS will look inside them and index the contents of the files. How sweet is that?
September 25, 2008 at 8:38 am
I want to query result inside files that is stored inside varbinary(max) as Word Document (.doc). I already have the column_type. I have tested query on nvarchar(Max) column for result ,it works ,but when with varbinary(Max) column ,it return result only in binary text not in readable text. I don’t know how to write sql query to select result from that binary content (.doc). Could anyone please help write down an example how to query the result from varbinary column by using this fullText search. thank on go.
March 15, 2009 at 10:41 pm
[...] Searching in SQL Server 2005 Lesson 1 – The Catalog Lesson 2 – The Indexes Lesson 3 – Using SQL Lesson 4 – Valid Data Types Lesson 5 – Advanced [...]
August 30, 2010 at 7:41 pm
Any chance, you will make good on your promise to write a blog entry on how to do FTS using XML column? I could really use it.
August 31, 2010 at 6:05 am
I haven’t gotten to it yet, but it’s dirt simple. Just create a full text index on the XML column like you would any other data type. Then when you do a full text search on the XML data, the full text engine is smart enough to ignore any words inside XML tags and only index the data between the tags.