Full Text Index In SQL Server

In this article you will learn about 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 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.
  1. Create a Full Text Catalog
  2. Create Full Text Index
  3. 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.
 
new catalog 
 

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.
 
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:
  1. CREATE FULLTEXT INDEX ON table_name  
  2. [ ( { column_name  
  3. [ TYPE COLUMN type_column_name ]  
  4. [ LANGUAGE language_term ]   
  5. [ STATISTICAL_SEMANTICS ]  
  6. } [ ,...n]   
  7. ) ]  
  8. KEY INDEX index_name  
  9. ON<catalog_filegroup_option> ]  
  10. WITH [ ( ] <with_option> [ ,...n] [ ) ] ]  
  11. [;]  
  12.   
  13. <catalog_filegroup_option>::=  
  14. {  
  15. fulltext_catalog_name  
  16. | ( fulltext_catalog_name, FILEGROUP filegroup_name )  
  17. | ( FILEGROUP filegroup_name, fulltext_catalog_name )  
  18. | ( FILEGROUP filegroup_name )  
  19. }  
  20.   
  21. <with_option>::=  
  22. {  
  23. CHANGE_TRACKING [ = ] { MANUAL | AUTO | OFF [, NO POPULATION ] }   
  24. | STOPLIST [ = ] { OFF | SYSTEM | stoplist_name }  
  25. | SEARCH PROPERTY LIST [ = ] property_list_name  
  26. }  
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
  1. CREATE FULLTEXTCATALOG New_CatalogASDEFAULT;  
  2. CREATE FULLTEXTINDEX ON dbo.Employee(EmployeeName TYPECOLUMN varchar LANGUAGE 1033,EmpSalary TYPECOLUMN varchar LANGUAGE 1033)  
  3. KEY INDEX UN_Pankaj  
  4. ON  
  5. 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
  1. SELECT TOP 10 tmski.Keyword_TextFROMdbo.TblMaster_Searching_Keyword_Infotmski  
  2. WHERE  
  3. FREE TEXT(Keyword_Text,'Hotel Above')  
Output
 
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
  1. SELECT TOP 10 tmski.Keyword_TextFROMdbo.TblMaster_Searching_Keyword_Infotmski  
  2. WHERE  
  3. 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).
 
Read more articles on SQL Server: