SQL: Handling of a Full Text Index

Analyse Current Index Situation

Old code (First support in SQL 2008, last support SQL 2012??)

exec sp_help_fulltext_columns
exec sp_help_fulltext_tables
exec sp_help_fulltext_catalogs

Modern Approach (from SQL 2008R2)

--These need a little work to make them meaningful
select * from sys.fulltext_index_columns
select * from sys.fulltext_catalogs
select * from sys.fulltext_indexes
 
--Added info
select * from sys.fulltext_stoplists
select * from sys.fulltext_system_stopwords t1 where language_id = 1033     --1033 = English
 
select * from sys.fulltext_languages t1 order by t1.[name]
select * from sys.fulltext_languages t1 order by t1.lcid

 

Mange a Full Text Index

This uses Stream's most commonly used index as the example, run on the ItemInfo table.

/*
--Drop a Full Text Catalog
DROP FULLTEXT INDEX ON ItemInfo
DROP FULLTEXT CATALOG ItemInfo
*/
 
--Rebuild a Full Text Catalog
CREATE FULLTEXT CATALOG
    ItemInfo as default
 
CREATE FULLTEXT INDEX ON ItemInfo (  
  ItemSearchText)  
KEY INDEX
    PK_ItemInfo ON ItemInfo
WITH
    stoplist = system,
    change_tracking auto;

 

SQL Server Setup

Apply the following to make Full Text Searches work in the way that Stream needs. 

We want to ignore the 'Stop Words', but still return a result. By default the setting is to ignore the Stop Word, and then NOT give any results.

eg. Search on "W F Williams".
By default this is broken into 3 words.  W and F are stop words and so no results found with an 'and' style search.
By applying the below, the W and F words are still not found, but they become excluded and so allow the result for "Williams" to carry through.

To achieve this outcome at the SERVER level (Stream's Standard)

sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
 
-- 1= Noise words (or stopwords) are transformed. They are ignored, and the rest of the query is evaluated.
sp_configure 'transform noise words', 1;  
RECONFIGURE;
GO


            
To achieve this outcome at the DATABASE level

ALTER FULLTEXT INDEX ON table_name SET STOPLIST [ = ] { OFF| SYSTEM | stoplist_name }

 

Analyse FTS Word Parser

When we don't understand what the heck is going on, it may be useful to understand how the MS SQL FTS engine parses a string as part of its processing.  This can be seen by running the following, with a few examples given to help show variations on the theme.

select * from sys.dm_fts_parser('"helo"', 1033, NULL, 0)
select * from sys.dm_fts_parser('"helo world"', 1033, NULL, 0)
select * from sys.dm_fts_parser('"helo-world"', 1033, NULL, 0)
select * from sys.dm_fts_parser('"SP-70S-C"', 1033, NULL, 0)
select * from sys.dm_fts_parser('(FORMSOF(INFLECTIONAL, "SP-70S-C") or "SP-70S-C*")', 1033, NULL, 0)

Login





Forgot password?
Create an Account