Full Text Index In SQL Server

Article

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 words or phrase searching. Before we can run full-text queries on a table, first we 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 contain 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 we need Full Text Index (FTI), if we can use like statement for searching

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

code

Above query is efficient for 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 provide better result compare to traditional like method.

When to use FTI over the LIKE statement

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

Create Full Text Index

Now, I will explain how to create full text index. 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.

Create a Full Text Catalog
Create Full Text Index
Populate the Index

Step 1: Create Full Text Catalog

Full Text catalog used for the full-text index. If we don’t specify the full text catalog then SQL Server will use the default catalog. Now we learn how to create a full text catalog.

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

Create

Now provide a name to full text catalog.

catalog

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

Step 2: Create Full Text Index

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

Create

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

Index

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

Index

Select change tracking.

Now select the full-text catalog for index.

Catalog

Catalog

Catalog

Catalog

Last image confirm that full text index is created successfully. Now we populate this full text index.

Step 3: Populate the Index

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

Index

Index

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 name of table
column_name	Define name of the columnincluded in the full-text index.
TYPE COLUMN type_column_name	Define the type ofcolumn(exavarchar,varbinary)
LANGUAGE language_term	Define he language of the datastored in column_name.
STATISTICAL_SEMANTICS	Creates the additional keyphrase and document similarity indexes that are part of statistical semanticindexing.
KEY INDEX index_name	Define the name of the uniquekey index on table_name.
fulltext_catalog_name	Define the full-text catalogused for the full-text index.
FILEGROUP filegroup_name	Creates the specifiedfull-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 thefull-text index will be propagated by SQL Server to the full-text index.
STOPLIST [ = ] { OFF \| SYSTEM \| stoplist_name }	Associates a full-textstoplist with the index.
SEARCH PROPERTY LIST [ = ] property_list_name	Associates a search propertylist 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, now we learn how to use these into search query for better performance. There are four principal T-SQL functions which allow one to interact with your Full-Text indices:

CONTAINS and FREETEXT Method.

CONTAINS and FREETEXT function 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 perform 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 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:

Output

Conclusion:

Use FULL Text Index search when you have a large volume of data and you want to perform 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).