Full Text Indexing in SQL Server 2012

In this article, we will see how to set up a full-text search index for SQL Server database.

In this article, we will see how to create a full-text search index for a SQL Server database. When we develop an application, in many cases we need to provide the search facility in our application. You can search records using keywords like "where" and "like" and there might be other ways. In this article, we will see the full-text search index technique to search records and the effect of it. So let's take a look at a practical example of how to create a full-text search index in SQL Server. The example is developed in SQL Server 2012 using the SQL Server Management Studio.
 

Creating Table in SQL Server

 
First I have created a table named News. The table News has a column Title as below:
 
Table-in-SQL-Server.jpg
 

Using Where and Like Predicates

 
After you have created a table named News with a column named Title you could construct these queries to search records:
  1. SELECT * FROM News WHERE Title = 'Statement'  
  2. SELECT * FROM News WHERE Title LIKE '%Statement%'  
Output
 
Searching-with-where-and-like-in-SQL-Server.jpg 
 
The above image does not display any records, in other words the "where" keyword is used for the full text of the column and the "like" keyword is used for parts of the column.
 
Now execute the following statement which contains the search text near the statement. Such as:
  1. SELECT * FROM News WHERE Title LIKE '%Statements%'  
Output
 
Searching-with-like-in-SQL-Server.jpg
 
The above image does not display any record, in other words the "like" keyword is used for the full text or part of the column. It does not support:
  1. Two words near each other
  2. Multiple words with distinct weightings
  3. A word or phrase close to the search word or phrase

Full-Text Search Index for SQL Server

 
First of all start SQL Server 2012 and select a database and right-click on the News table and select Full-Text Index to define the Full-Text Index.
 
Full-text-index-option-in-SQL-Server.jpg 
 
This would open the full-text index wizard window.
 
full-text- index- wizard-in-SQL-Server.jpg 
 
Now click on the Next Button. The unique index, the default, is correct.
 
unique-index-in-sql-Server.jpg 
 
Now click on the Next Button and select columns from the News table to create a Full Text Search Index.
 
Select-Columns-from-for-searching-in-SQL-Server.jpg 
 
Now click on the Next Button and the automatically the default is correct.
 
Default-automatically-option-in-SQL-Server.jpg 
 
Now click on the Next Button and give the name of the catalog.
 
Catalog-Name-In-SQL-Server.jpg 
 
Now click on the Next Button.
 
Next-Image-In-SQL-Server.jpg 
 
Now click on the Next Button. This would open the full-text index wizard window with the work done.
 
full text-index-wizard-window-with-performed-work-in-SQL-Server.jpg 
 
Now click on the Finish Button.
 
full text-index-wizard-Progress-in-SQL-Server.jpg 
 
Now click on the Close Button and you will see the catalog created after expanding full-text catalogs.
 
Created-Catalog-in-SQL-Server.jpg 
 
Now right-click on the News table and select Full-Text Index to start Full Population.
 
Start-Full-Population-in-SQL-Server.jpg 
 
Now click on the Start Full Population.
 
Performed-Population-in-SQL-Server.jpg 
 

Searching Using a Full-Text Index 

 
Now you're ready to actually do some searches. Four T-SQL predicates are involved in full-text searching:
  • FREETEXT: This finds the word or words you give it anywhere in the search column.
  • FREETEXTTABLE: This works like freetext except that it returns its results in a Table object.
  • CONTAINS: You can search for one word "near" another this way.
  • CONTAINSTABLE: This works like contains except that it returns its results in a Table object.
Example
 
I added a full-text index to the Title column in the News table and using the FreeText Predicate:
  1. SELECT * FROM News WHERE Title LIKE '%Statements%'  
  2.   
  3. SELECT * FROM News WHERE FREETEXT (Title, 'Statement')  
Output
 
Full-Text-Index-In-SQL-Server.jpg
 
Example
 
I added a full-text index to the Title column in the News table and using the Contains Predicate:
  1. SELECT * FROM News WHERE Contains(Title, 'Statements')  
  2.   
  3. SELECT * FROM News WHERE FreeText(Title, 'Statements')  
Output
 
Full-Text-Index-with-Contains-In-SQL-Server.jpg