Reader Level:
ARTICLE

Full Text Indexing in SQL Server 2012

Posted by Rohatash Kumar Articles | SQL Server October 18, 2012
In this article we will see how to set up a full text search index for SQL Server database.
  • 0
  • 0
  • 11606

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:

SELECT * FROM News WHERE Title = 'Statement'

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:

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:

SELECT * FROM News WHERE Title LIKE '%Statements%'

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:

SELECT * FROM News WHERE Contains(Title, 'Statements')

SELECT * FROM News WHERE FreeText(Title, 'Statements')

Output

Full-Text-Index-with-Contains-In-SQL-Server.jpg

COMMENT USING

Trending up