Welcome!

Identity Management Tips, Thoughts and Opinions

Matthew Pollicove

Subscribe to Matthew Pollicove : eMailAlertsEmail Alerts
Get Matthew Pollicove via: homepageHomepage mobileMobile rssRSS facebookFacebook twitterTwitter linkedinLinkedIn


Blog Feed Post

SQL Server Fulltext search updates

If you are using Fulltext search with a SQL Server implementation of IDM, be advised that there is a new installation guide that contains updated information about Fulltext search.  I was recently passed this information that you might find helpful...

There is a noiseword (2005)/stop-word(2008) list that contains words thatare not indexed. Most of these makes sense (a, the, or, and, etc.) and does notaffect searches in IdM. Our naming of privileges with the word "only"was slightly more unfortunate as its also considered a noiseword. This requirescustomization of the noiseword/stopword list for customers that want to searchfor the repository privilege.

 

Customizing stopwords in SQLServer 2008:



SQL Server 2008 uses stopwords stored in the database. To customize the listyou need to make a copy of the system stopwords list and assign it to be usedwith the IdM full-text index (ftfull). This can be done using these commands orfrom the user interface of the SSMS.

CREATE FULLTEXT STOPLIST idmStopList FROM SYSTEM STOPLIST;
-- Remove the words you want to include in the index:
ALTER FULLTEXT STOPLIST idmStopList DROP 'only' LANGUAGE 1033;
ALTER FULLTEXT INDEX ON mxi_values SET STOPLIST idmStopList

Its also possible to view the stopwords using queries. An example listinglanguages blocking the word "only" follows:

-- To list all entries of 'only' stopwords in the stoplist (can be manylanguages):
SELECT * FROM sys.fulltext_stopwords WHERE stoplist_id = (SELECT stoplist_idFROM sys.fulltext_stoplists where name = 'idmStopList') and stopword = 'only'



-- To test a stoplist
SELECT special_term, display_term FROM sys.dm_fts_parser (' "a text likesystem priv ad only somethingsomething" ', 1033,(SELECT stoplist_id FROMsys.fulltext_stoplists where name = 'idmStopList'), 0)







Here we see that "a" and "like" are considered noise andnot indexed, while "only" is indexed for exact matches.

Customizing noisewords in SQLServer 2005:


Modify the noiseZZZ.txt files in sql server folder, remove "only"and other things you want to include from ENG,ENU (and others) and then drop& recreate fulltext index:

drop fulltext index on mxi_values
drop fulltext catalog ftfull
CREATE FULLTEXT CATALOG ftfull WITH ACCENT_SENSITIVITY=OFF AS DEFAULT;
CREATE FULLTEXT INDEX ON mxi_values(searchvalue) KEY INDEXIX_MXI_VALUES_Value_ID;
 




Hope you found this helpful! Thanks again to the folks that passed this info along!

Read the original blog entry...

More Stories By Matthew Pollicove

Matt Pollicove is an Identity Management architect, engineer, trainer, project manager, author and blogger with experience in user account provisioning, data synchronization, virtual directory and password management solutions. As a MaXware Technical Consultant and later as a System Engineer, he worked extensively with MaXware (now SAP) software products in large customer environments. In the past Matt has worked with several leading national and international consulting firms and is currently a Sr. Principal Consultant for Commercium Technologies. He is currently the Practice Lead for SAP NetWeaver Identity Management and SailPoint IIQ.