Monday, November 10, 2008

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.


WHERE TABLE_NAME = 'FulltextTest')

DROP 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...')


-- Enable full-text searching in the database.

EXEC sp_fulltext_database 'enable'


-- 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


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



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

SELECT article_title

FROM FulltextTest

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


No comments:

Post a Comment

MS CRM 2011 KB Article customization Issue.

Recently I have encountered some issue while customizing Kb Article Entity. I was doing following configuration in Article form. 1. Add Ba...