How to Use Full Text Search in SQL Server 2008

This article demonstrates how to use full text catalog and full text search in SQL Server 2008.

Definition From MSDN

Full-Text Search in SQL Server lets users and applications run full-text queries against character-based data in SQL Server tables. Before you can run full-text queries on a table, the database administrator must create a Full-Text Index on the table. The Full-Text Index includes one or more character-based columns in the table. These columns can have any of the following data types: char, varchar, nchar, nvarchar, text, ntext, image, xml, or varbinary(max) and FILESTREAM. Each Full-Text Index indexes one or more columns from the table, and each column can use a specific language.

Full-text queries perform linguistic searches against text data in Full-Text Indexes by operating on words and phrases based on rules of a particular language such as English or Japanese. Full-text queries can include simple words and phrases or multiple forms of a word or phrase. A full-text query returns any documents that contain at least one match (also known as a hit). A match occurs when a target document contains all the terms specified in the full-text query, and meets any other search conditions, such as the distance between the matching terms.

Getting Started

First of all start SQL Server 2008 and select database and expand storage and right-click on Full Text Catelog and select New Full Text Catelog.



Image 1.

Enter the catalog name and click OK.



Image 2.

You will see the catalog created after expanding full text catalogs.



Image 3.

After creating the full text catalog now it is time to create a Full-Text Index.

Note - If the Full-Text Index feature is disabled then you need to execute this query:

use NORTHWND

 

CREATE FULLTEXT CATALOG ftCatalog AS DEFAULT;

GO

 

EXEC NORTHWND

Now right-click on the table and select Full-Text Index and click Define Full-Text Index.



Image 4.



Image 5.



Image 6.



Image 7.

Image 8.

Image 9.

Image 10.

Image 11.

Image 12.

After creating the Full-Text Index successfully now Start Full Population.



Image 13.

Image 14.

We are done here; now it is time to execute the full-text search.



Image 15.