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.
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.
- 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.
CREATE FULLTEXT CATALOG FCAT_TEMP --Full text catalog GO --Full text index CREATE FULLTEXT INDEX ON [dbo].[Survey](PrSurvey) -- [TableName](ColumnName) KEY INDEX [PK_Survey_id] --[Primary Key Name] ON [FCAT_TEMP] -- [Fulltext Catalog]
SELECT * FROM Survey WHERE CONTAINS(PrSurvey, '"Mountain" AND "United Security"') SELECT * FROM Survey WHERE CONTAINS(PrSurvey, '"Mountain" OR "Touring"') SELECT * FROM Survey WHERE CONTAINS(PrSurvey, 'Mountain')
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””
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.
--Full text stop list CREATE FULLTEXT STOPLIST STOPLIST_TEMP FROM SYSTEM STOPLIST; --Semi-colon (;) is must GO SELECT * FROM sys.fulltext_stopwords WHERE language = 'ENGLISH' GO --Full text index creation on comment column CREATE FULLTEXT INDEX ON [dbo].[SurveyComment](Comment) -- [TableName](ColumnName) KEY INDEX [PK_SurveyComment] --[Primary Key Name] ON [FCAT_TEMP] -- [Fulltext Catalog] GO SELECT * FROM [SurveyComment] WHERE CONTAINS([Comment], '"three" NEAR "wheeled"')
ADDING or DROPPING STOP WORDS:
You may want to customize the STOP WORD list by removing or adding your own words.
--Adding a key word into the stop list ALTER FULLTEXT STOPLIST STOPLIST_TEMP ADD 'SQL' LANGUAGE ENGLISH; --Dropping a key word into the stop list ALTER FULLTEXT STOPLIST STOPLIST_TEMP DROP 'SQL' LANGUAGE ENGLISH;
IMP: Full text catalog has to be rebuilt after adding or dropping a STOP WORD.