FULLTEXT INDEX / FULLTEXT SEARCH

 

Use FullText Search to run full-text queries against character-based data in SQL Server tables . To run full-text queries on a table, one needs to 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.

For more information on FullText Index , see the following link

http://msdn.microsoft.com/en-IN/library/ms142571.aspx

How to Check if FullText Index is enabled in SQL Server :

Execute the following query to verify if “FullText Indexing” is installed or not in the server.

select SERVERPROPERTY('IsFullTextInstalled')

If the query returns 1 , then FullText Indexing is installed and one can create FullText Index on the required table. If it returns 0 , then its not installed , and one needs administrative privileges to install it.

CREATE FULLTEXT INDEX ON A TABLE :

1)Before creating FullText Index on a table , one needs to create

- a Unique Index on that table and

- Catalog for FullText Index.

2)Syntax for creating Full Text Index

CREATE FULLTEXT INDEX ON table_name [ ( { column_name   TYPE COLUMN type_column_name ]                       [ LANGUAGE language_term ]                      [ STATISTICAL_SEMANTICS ] } [ ,...n]       ) ]    KEY INDEX index_name   [ ON <catalog_filegroup_option> ] [ WITH [ ( ] <with_option> [ ,...n] [ ) ] ] [;]

Sample code for creating and dropping Full Text Index on a table :

/*CREATE TABLE*/ CREATE TABLE CandidateData ( CandidateId INT NOT NULL, Resume VARCHAR(MAX) ) ON [PRIMARY] GO /*CREATE UNIQUE INDEX ON THE TABLE*/ CREATE UNIQUE CLUSTERED INDEX PK_CandidateData ON CandidateData(CandidateId); /*CREATE FULLTEXT CATALOG*/ CREATE FULLTEXT CATALOG ft AS DEFAULT; /*CREATE FULLTEXT INDEX*/ CREATE FULLTEXT INDEX ON CandidateData(Resume)    KEY INDEX PK_CandidateData    ON ft WITH CHANGE_TRACKING AUTO; /*START FULLTEXT SEARCH*/ SELECT * FROM CandidateData WHERE CONTAINS(Resume, 'SQL SERVER') /*DROP FULL TEXT INDEX*/ DROP FULLTEXT INDEX ON CandidateData

Note : Full-text queries use a small set of Transact-SQL predicates (CONTAINS and FREETEXT) and functions (CONTAINSTABLE and FREETEXTTABLE)