The full-text search capabilities of SQL Server 2008 make it possible to search text across multiple columns, weight search terms, and search by proximity. A full-text query can be much faster than the equivalent LIKE query.
The first step is to create a full-text catalog and index and to populate the index. The Full-Text Indexing Wizard takes you through this step-by-step, and allows you to create a catalog if necessary and define population schedules. (There are step-by-step instructions on using the wizard here). If the ‘Full-Text index’ option is greyed-out on the table’s context menu, the full-text component is probably not installed. In order to check, you can execute
This will return 1 if it is installed.
Once you have this set up you can start to query the index. Examples of the functions available can be found at
To query the index from within an application using SubSonic 3 you can use the
CodingHorror object to execute inline SQL – see here. Note that the example is parameterized to avoid SQL injection attacks.