Chapter 13. Full-text searching
Search is everywhere. In addition to the powerful search engines available to us, it seems like every website we visit has a search box for searching within that site. Wouldn’t it be great to incorporate search within your applications? Fortunately, SQL Server provides a powerful text search engine that’s as easy to use as one-two-three!
Before we begin the step-by-step process of creating and using full-text indexes, there are a few fundamentals that you’ll need to understand. Full-text search isn’t a fancy way of doing a LIKE search with SQL. Instead, every word is placed into a special type of index called a full-text index. These indexes are organized and stored in full-text catalogs, which act as containers to organize our indexes.
Each word in a full-text index also includes a unique key for that record. You should note that in order to full-text index a table, SQL Server requires the table to have a unique, single-column key. This single-column key is used as part of the ranking functions we’ll cover later in this chapter.
All of the text-based data types are eligible for full-text searching. The complete list is char, nchar, varchar, nvarchar, text, ntext, xml, image, and varbinary(max). According to online documentation from Microsoft, text, ntext, and image data types will be deprecated in future versions of SQL Server, so I suggest avoiding these if you can.
