Setting up and using Full Text Search in SQL Server 2008

The Full-text search feature of SQL Server provides a fast means of searching unstructured text and binary formatted text such as Word documents. A major enhancement in SQL Server 2008 is indexes are stored inside database filegroups, rather than the file system. In addition the query processor for full-text search is integrated with the database engine for better performance.

Performing a full-text search on a column requires 3 main steps:

- Create a FULLTEXT catalog for a database and associate it with a FILEGROUP.
- Create a FULLTEXT index on a column or columns in a table.
- Query indexed columns using FREETEXT or CONTAINS predicates.

Create a new FILEGROUP

It is recommended that a separate FILEGROUP be created for FULLTEXT searches (for example for performance and maintainability) so this example will start by creating a new FILEGROUP and associating a file with it:

ALTER DATABASE msnotes ADD FILEGROUP msnotesFTSearch
GO
ALTER DATABASE msnotes ADD FILE (NAME = ‘msnotesFT’, FILENAME =
   ‘c:\program files\Microsoft SQL Server\MSSQL10_50.MYSERVER\MSSQL\DATA\msnotesFT.ndf’)
   TO FILEGROUP msnotesFTSearch
GO

Create a FULLTEXT catalog

USE msnotes
GO
CREATE FULLTEXT CATALOG noteDataFTC on FILEGROUP msnotesFTSearch
GO

Create a FULLTEXT index

CREATE FULLTEXT INDEX ON dbo.notes(note_data)
   KEY INDEX notes_IDX
   ON noteDataFTC
   WITH CHANGE_TRACKING = AUTO
GO

Run a query

A simple way to query a column which has a full-text index is to use the FREETEXT function. E.g.

SELECT category, note_data
   FROM categories, notes
   WHERE note_cat = catidx
   AND FREETEXT(note_data, ‘FILESTREAM’)
GO

For more complex and wildcard queries use the CONTAINS function, e.g.

SELECT category, note_data
   FROM categories, notes
   WHERE note_cat = catidx
   AND CONTAINS(note_data,’"FILESTREA*"’)
GO

Resources

Book: MCTS Self-Paced Training Kit (Exam 70-432): Microsoft® SQL Server® 2008 – Implementation and Maintenance by Mike Hotek.

White paper: SQL Server 2008 Full-Text Search: Internals and Enhancements by Fernando Azpeitia Lopez.

About these ads
This entry was posted in Uncategorized. 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