MS-SQL - Full Text Index in MS-SQL Server

Full Text Index helps to perform complex queries against character data.  These queries can include word or phrase searching. We can create a full-text index on a table or indexed view in a database. Only one full-text index is allowed per table or indexed view. The index can contain up to 1024 columns.


Let see how to create full text search index in MS-SQL Server.For this Example i will take Northwind Database.


Step 1
Download Northwind database from the following link.
http://www.microsoft.com/downloads/details.aspx?FamilyID=06616212-0356-46a0-8da2-eebc53a68034&displaylang=en

Step 2
Attach a Northwind database into MS-SQL server.


Step 3
Create a Full Text Catalog,it is look like this

Click on Image for better view


Specify Full Text Catalog Name,it is look like this

Click on Image for better view


Now Full Text Catalog is Ready,it is look like this

Click on Image for better view


Step 4
Create a Full Text Index,it is look like this

Click on Image for better view

Full Text Index wizard will open, it is look like this

Click on Image for better view


Select Unique Index for the table,it is look like this.
Note. - In Northwind database there is no primary key on Employees table so create primary key on Empoyees table before selecting Unique Index for Full Text Index.

Click on Image for better view


Select a Table Columns for Full Text Queries,it is look like this

Click on Image for better view


Select Change Tracking,it is  look like this

Click on Image for better view


Select Catalog Name,it is look like this

Click on Image for better view


Define Population Schedules(Optional),it is look like this

Click on Image for better view


Full Text Index Description,it is look like this

Click on Image for better view

Click on Image for better view


Step 5
Populate the Index,it is look like this

Click on Image for better view

Click on Image for better view


As the full text index is created and populated,now we can write a query for searching records on desired table which provide better performance.


Step 6
For Example,we will find Employee Records who has 'Manager' in their Title.


FREETEXT( ) Is predicate used to search columns containing character-based data types. It will not match the exact word, but the meaning of the words in the search condition. When FREETEXT is used, the full-text query engine internally performs the following actions on the freetext_string, assigns each term a weight, and then finds the matches.

• Separates the string into individual words based on word boundaries (word-breaking).
• Generates inflectional forms of the words (stemming).
• Identifies a list of expansions or replacements for the terms based on matches in the thesaurus.

USE Northwind

SELECT * FROM Employees
 WHERE FREETEXT(Employees.Title,'Manager')

Click on Image for better view


CONTAINS( ) is similar to the Freetext but with the difference that it takes one keyword to match with the records, and if we want to combine other words as well in the search then we need to provide the “and” or “or” in search else it will throw an error.

USE Northwind

SELECT * FROM Employees
 WHERE CONTAINS(Employees.Title,'President OR Manager')
 
SELECT * FROM Employees
 WHERE CONTAINS(Employees.Title,'Sales AND Representative')

Click on Image for better view


Full text indexing is a great feature that solves a database problem, the searching of textual data columns for specific words and phrases in SQL Server databases. Full Text Index can be used to search words, phrases and multiple forms of word or phrase using FREETEXT() and CANTAINS() with “and” or “or” operators while LIKE Operator will search for the words that contain part of the search string.