Full Text Indexing in SQL Server

Sql Server’s Full-Text search can let a developer create some very slick features disturbingly easily. And, unlike many other Full-Text implementations, it is not limited to plain text fields. It can also search within binary fields with the proper setup considerations. Needless to say, there are a few protips to making Full-Text indexes work and taking advantage of them. In this post, I will tell you how to get Full-Tex.

Getting Started

First, you are going to need a copy of Sql Server 2000 or 2005 Standard edition. MSDE or Sql Express do not have Full-Text capabilities. In order to enable Full-Text search, you must do a few things.

• Enable Full-Text search on the database.
• Create a Full-Text catalog.
• Enable Full-Text searching on specific columns in your data.

Full Text Indexing in SQL Server 2000

• Create and populate a table.
• Enable the pubs database for full-text searching
• Create a full-text catalog.
• Register the new table and certain columns in it for full-text search.
• Populate the new full-text catalog with full-text index information from the new
• tableExecute a full-text query against the new table.

USE Pubs

-- Create and populate a table.

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_NAME = 'FulltextTest')

DROP TABLE FulltextTest

GO

CREATE TABLE FulltextTest

( article_id int IDENTITY(100,1)

CONSTRAINT PK_title_id PRIMARY KEY,article_title nvarchar(200))

INSERT FulltextTest (article_title) VALUES (N'Steven Buchanan has always enjoyed ice skating.')

INSERT FulltextTest (article_title) VALUES (N'Elvis Stoiko: The best male figure skater')

INSERT FulltextTest (article_title) VALUES (N'Steven Buchanan On Ice: Skating Reaches Tops in Public Opinion Poll')

INSERT FulltextTest (article_title) VALUES (N'Last night, Steven Buchanan skated on the ice!! Skating fans cheer!')

INSERT FulltextTest (article_title) VALUES (N'Ice-skating brings out the best in Steven. Buchanan exults in first victory...')

GO

-- Enable full-text searching in the database.

EXEC sp_fulltext_database 'enable'

GO

-- Create a new full-text catalog.

EXEC sp_fulltext_catalog 'StevenBCatalog', 'create' GO

-- Register the new table and column within it for full-text querying,

-- then activate the table.

EXEC sp_fulltext_table 'FulltextTest', 'create', 'StevenBCatalog', 'PK_title_id'

EXEC sp_fulltext_column 'FulltextTest', 'article_title', 'add'

EXEC sp_fulltext_table 'FulltextTest', 'activate'GO

-- Start full population of the full-text catalog. Note that it is

-- asynchronous, so delay must be built in if populating a

-- large index.

EXEC sp_fulltext_catalog 'StevenBCatalog', 'start_full'

WHILE (SELECT fulltextcatalogproperty('StevenBCatalog','populatestatus'))<>0

BEGINWAITFOR DELAY '00:00:02'

-- Check every 2 seconds to see if full-text index population is complete.

CONTINUE

END

-- Execute a full-text query against the new table.

SELECT article_title

FROM FulltextTest

WHERE CONTAINS(article_title, ' "Steven Buchanan" AND "ice skating" ')

GO