FULLTEXT INDEX IN SQL SERVER

Full-text queries perform linguistic 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 Hindi or Gujarati etc. Full-text queries can include simple words and phrases or multiple forms of a word or phrase. A full-text query returns any documents 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.

You must have got this question in your mind now or before that, how Google and other search engines are working over finding the queries based on keywords in just fraction of a second? Ans: Actually because of full text indexing, it’s possible today!  They crawl the whole content from websites and stored them in some sort of index.

FullTextIndex
FULLTEXT INDEX WORKFLOW IN SQL SERVER

We all know how to search a keyword in a string using LIKE operator but unfortunately the same does not work for LOBs like XML, TEXT or NTEXT etc.. We need full text indexes to have keywords find in an efficient way or a better search engine can be designed by a proper full text index.

Download sample scripts used in this lab

FullTextIndex_01

  • Microsoft SQL Server has a special windows service to handle the whole FULLTEXT thing.
  • Make sure “SQL Full-text Filter Daemon Launcher ” service is running. To check RUN –> Services.msc
  • We have to have a NOT NULL UNIQUE KEY or PRIMARY KEY on the table for FULLTEXT INDEX.
  • We have to create a FULLTEXT CATALOG to accommodate FULLTEXT INDEX.

FullTextIndex_02

FullTextIndex_03

STOP WORDS:

You must be wondering, what is it? It’s nothing but a list of common words in English or in any other language, which will be skipped during keyword search. It’s also known as NOISE WORDS in terminology.

Like “what is the use of full text index in sql server
STOP LIST key words are “what”, “is”, “the”, “of”, “in””

FullTextIndex_05

It will be very hard to find the exact string match over millions of LOB data and it may be a performance killer in that case also, so what fulltext index does, it ignores the STOP LIST words and finds the remaining phrases, instead of searching “what is the use of full text index in sql server”, it will try to search only “full text index in sql server” by ignoring the STOP LIST keys, hence we get a very comprehensive result set of  our desired search.

FullTextIndex_06

FullTextIndex_07

ADDING or DROPPING STOP WORDS:

You may want to customize the STOP WORD list by removing or adding your own words.

 IMP: Full text catalog has to be rebuilt after adding or dropping a STOP WORD.

FullTextIndex_08

 

Prasad Sahoo

Prasad Sahoo, is an enthusiast of Microsoft technologies in general and a passionate database professional, mainly focusing on SQL Server performance tuning and business intelligence. He has contributed a long time of his career working on SQL Server and other RDBMS. If he is not busy with SQL stuff then he must be watching some Hollywood movies.

One thought on “FULLTEXT INDEX IN SQL SERVER

Leave a Reply

%d bloggers like this: