Reader Level:
ARTICLE

How to Use Full Text Search in SQL Server 2008

Posted by Raj Kumar Articles | SQL August 22, 2012
This article demonstrates how to use full text catalog and full text search in SQL Server 2008.
  • 2
  • 0
  • 4994

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.

TextSrc1.jpg

Image 1.

Enter the catalog name and click OK.

TextSrc2.jpg

Image 2.

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

TextSrc3.jpg

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.

TextSrc4.jpg

Image 4.

TextSrc5.jpg

Image 5.

TextSrc6.jpg

Image 6.

TextSrc7.jpg

Image 7.

TextSrc8.jpg

Image 8.

TextSrc9.jpg

Image 9.

TextSrc10.jpg

Image 10.

TextSrc11.jpg

Image 11.

TextSrc12.jpg

Image 12.

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

TextSrc13.jpg

Image 13.

TextSrc14.jpg

Image 14.

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

TextSrc15.jpg

Image 15.

COMMENT USING

Trending up