Full-Text Search with SQL Server 2008

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
SELECT FullTextServiceProperty('IsFullTextInstalled')
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.

Advertisements

About Jennifer Phillips Campbell

Software Developer and Medieval Historian
This entry was posted in SQL, SQL Server. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s