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 Full-Text Index using SQL Command

Use the following command syntax to create the Full Text Index.

Syntax

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  
}

Parameters

Parameter	Description
table_name	Define the name of the table
column_name	Define the name of the column included in the full-text index.
TYPE COLUMN type_column_name	Define the type of column(exavarchar,varbinary)
LANGUAGE language_term	Define the language of the data stored in column_name.
STATISTICAL_SEMANTICS	Creates the additional keyphrase and document similarity indexes that are part of statistical semantic indexing.
KEY INDEX index_name	Define the name of the unique key index on table_name.
fulltext_catalog_name	Define the full-text catalog used for the full-text index.
FILEGROUP filegroup_name	Creates the specified full-text index on the specified filegroup.
CHANGE_TRACKING [ = ] { MANUAL | AUTO | OFF [ , NO POPULATION ] }	Specifies whether changes(updates, deletes, or inserts) made to table columns that are covered by the full-text index will be propagated by SQL Server to the full-text index.
STOPLIST [ = ] { OFF | SYSTEM | stoplist_name }	Associates a full-text stop list with the index.
SEARCH PROPERTY LIST [ = ] property_list_name	Associates a search property list with the index.

Example

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  

After creating the FULL Text Catalog and Full Text Index, we now learn how to use these in search queries for better performance. There are four principal T-SQL functions that allow one to interact with your Full-Text indices:

CONTAINS and  FREETEXT Method

CONTAINS and FREETEXT functions return a boolean value, meaning we could use them directly in a WHERE clause. The remaining two return a two-column table—KEY and RANK, allowing one to manage ranked searches.

FREETEXT

FREETEXT T-SQL function performs predicate searches for values that match the meaning and not just the exact wording 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.

Example

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

Output

CONTAINS

CONTAINS searches for precise or fuzzy (less precise) matches to single words and phrases, words within a certain distance of one another, or weighted matches in SQL Server. It 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.

CONTAINS can search for

• A word or phrase.
• The prefix of a word or phrase.
• A word near another word.
• A word is inflectionally generated from another (for example, the word drive is the inflectional stem of drives, drove, driving, and driven).
• A word that is a synonym of another word using a thesaurus (for example, the word "metal" can have synonyms such as "aluminum" and "steel").

Example

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

Output

Conclusion

Use FULL-Text Index search when you have a large volume of data, and you want to perform a search for textual data columns for specific words and phrases. Full-Text Index can be used to search words, phrases, and multiple forms of a word or phrase using FREETEXT (), CONTAINS () with “and” or “or” operators (FREETEXT, CONTAINS).

Read more articles on SQL Server.

• Create User-Defined Data Types In SQL Server
• Stretching SQL Server 2016 Tables To The Azure Cloud