Full Text Index In SQL Server

Introduction

Full-text search is one of the needs of an application to find data in a database. The full-Text Index feature in SQL Server allows you to run a full text search on a database table. In this article, we will learn about full-text index in SQL Server, including what full-text index is, how to create a full-text search index, and other use cases.

What is Full-Text Index in SQL Server?

Full-Text Search in SQL Server lets users and applications run full-text queries against character-based data in SQL Server tables. Full-Text index helps to perform complex queries against character data. These queries can include word or phrase searching. Before we can run full-text queries on a table, we first need to create a full-text index on the table. Only one full-text index is allowed per table, and this index can contain up to 1024 columns. 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, char, nvarchar, text, ntext, image, XML, or varbinary.

Full-text queries perform 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 document that contains 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.

Why do we need a Full Text Index (FTI) if we can use a statement for searching?

Let us consider a scenario; I have a table with column name data. What query will we use if we want to search for the name ‘smith’ in the data column, basically, we use the command below.

The above query is efficient for the above scenario, but what if you're not looking for an exact match? FTS has some better algorithms for matching data, as does some better statistics on variations of names. Therefore, FTS can provide better performance for matching Smith, Smythe, Smithers, etc., when you look for Smith. In such a case, FTS provides better results compared to the traditional like method.

When to use FTI over the LIKE statement?

• A word or phrase close to the search word or phrase
• When the result size is several hundred thousand
• Millions of rows, each with a string like "wordAwordBwordC..."
• Any word derived from a particular root (for example, run, ran, or running)

How to Create a Full-Text Index?

Now, I will explain how to create a full-text index. But, first, we will read two methods to create the full-text index, using manually and using the SQL command.

Create Full-Text Index Manually

The following steps are performed to create the Full Text Index.

1. Create a Full-Text Catalog
2. Create Full-Text Index
3. Populate the Index

1. Create a Full-Text Catalog

The full-text catalog is used for the full-text index. If we don’t specify the full-text catalog, then SQL Server will use the default catalog. So now we have learned how to create a full-text catalog.

To create a full-text catalog, select your database, go to the Storage folder, right-click on Full-Text Catalog, and select the New Full-Text Catalog option.

Now provide a name for the full-text catalog.

You can see that a new catalog has been created in the Storage folder.

2. Create Full-Text Index

To create a full-text index choose your table and right-click on that table and select the “ Define Full-Text Index” option.

Now select Unique Index. It is compulsory that for “Full-Text Index” table must have at least one unique index.

Select columns name and language types for columns. You can only select character-based and image-based columns.

Select change tracking.

Now select the full-text catalog for the index.

The last image confirms that the full-text index is created successfully. Now we populate this full-text index.

3. Populate the Index

To populate the index, right-click on the table and select the “Start Full Population” option.

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] [ ) ] ]  
[;]  
  
<catalog_filegroup_option>::=  
{  
fulltext_catalog_name  
| ( fulltext_catalog_name, FILEGROUP filegroup_name )  
| ( FILEGROUP filegroup_name, fulltext_catalog_name )  
| ( FILEGROUP filegroup_name )  
}  
  
<with_option>::=  
{  
CHANGE_TRACKING [ = ] { MANUAL | AUTO | OFF [, NO POPULATION ] }   
| STOPLIST [ = ] { OFF | SYSTEM | stoplist_name }  
| SEARCH PROPERTY LIST [ = ] property_list_name  
}

CREATE FULLTEXTCATALOG New_CatalogASDEFAULT;  
CREATE FULLTEXTINDEX ON dbo.Employee(EmployeeName TYPECOLUMN varchar LANGUAGE 1033,EmpSalary TYPECOLUMN varchar LANGUAGE 1033)  
KEY INDEX UN_Pankaj  
ON  
New_Catalog  

SELECT TOP 10 tmski.Keyword_TextFROMdbo.TblMaster_Searching_Keyword_Infotmski  
WHERE  
FREE TEXT(Keyword_Text,'Hotel Above')  

SELECT TOP 10 tmski.Keyword_TextFROMdbo.TblMaster_Searching_Keyword_Infotmski  
WHERE  
CONTAINS(Keyword_Text,'Hotel OR Above')