Advanced Queries for Using SQL Server 2008 Full Text Search StopWords / StopLists

Yesterday I covered the basics around StopWords/StopLists in SQL Server 2008. Today we’ll look at some advanced queries you can use to get more information back about FullText Indexes, StopWords, and StopLists.

The first query simply returns a list of all of the catalogs in the system.

– Get current list of full text catalogs

select [name] as CatalogName

    , path

    , is_default

  from sys.fulltext_catalogs

 order by [name];

 

The next query returns a list of all the StopLists.

 – Get the list of StopLists

 select stoplist_id

      , name

   from sys.fulltext_stoplists;

 

This query returns a list of StopWords in the database. Note the linking to get the associated StopList name and language.

 – Get list of StopWords

 select sl.name as StopListName

      , sw.stopword as StopWord

      , lg.alias as LanguageAlias

      , lg.name  as LanguageName

      , lg.lcid  as LanguageLCID

   from sys.fulltext_stopwords sw

   join sys.fulltext_stoplists sl

    on sl.stoplist_id = sw.stoplist_id

   join master.sys.syslanguages lg

    on lg.lcid = sw.language_id;

 

This next query gets a list of all of the stopwords that ship with SQL Server 2008. This is a nice improvement, you can not do this in SQL Server 2005.

– Get a list of the System provided stopwords  

select ssw.stopword

    , slg.name

  from sys.fulltext_system_stopwords ssw

  join sys.fulltext_languages slg

    on slg.lcid = ssw.language_id;

 

My next query returns a list of all the Full Text Indexes in the database.

– List full text indexes

select c.name as CatalogName

    , t.name as TableName

    , idx.name as UniqueIndexName

    , case i.is_enabled

        when 1 then ‘Enabled’

        else ‘Not Enabled’

       end as IsEnabled

    , i.change_tracking_state_desc

    , sl.name as StopListName

  from sys.fulltext_indexes i

  join sys.fulltext_catalogs c

    on i.fulltext_catalog_id = c.fulltext_catalog_id

  join sys.tables t

    on i.object_id = t.object_id

  join sys.indexes idx

    on i.unique_index_id = idx.index_id

       and i.object_id = idx.object_id

  left join sys.fulltext_stoplists sl

    on sl.stoplist_id = i.stoplist_id

 

 

This query returns a list of all the document types SQL Server 2008 understands when they are placed in a varbinary(max) field.

– List all of the document types SQL Server 2008 will understand in varbinary(max) field

select document_type

    , path

    , [version]

    , manufacturer

  from sys.fulltext_document_types;

 

If your full text performance begins to suffer over time, you might want to check and see how many fragments exist. If you have multiple closed fragments, you should consider doing a REORGANIZE on the index (using alter fulltext index). This query will tell you how many fragments exist for your full text index.

– See how many fragments exist for each full text index.

– If multiple closed fragments exist for a table do a REORGANIZE to help performance

select t.name as TableName

    , f.data_size

    , f.row_count

    , case f.status

        when 0 then ‘Newly created and not yet used’

        when 1 then ‘Being used for insert’

        when 4 then ‘Closed ready for query’

        when 6 then ‘Being used for merge inpurt and ready for query’

        when 8 then ‘Marked for deletion. Will not be used for query and merge source’

        else ‘Unknown status code’

       end

  from sys.fulltext_index_fragments f

  join sys.tables t on f.table_id = t.object_id;

 

There you go, a handful of powerful queries to help you query and maintain the state of your full text indexes.

About these ads

3 Responses to “Advanced Queries for Using SQL Server 2008 Full Text Search StopWords / StopLists”

  1. Aaron Says:

    This is somthing I have been looking for a long time. Thanks!!!

  2. SQL Server Full Text Searching at the Atlanta Code Camp « Arcane Code Says:

    [...] Can you hear me now? Checking to see if FTS is installed. Exploring SQL Servers FullTextCatalogProperty Function Using the ObjectPropertyEx Function Using FORMSOF in SQL Server Full Text Searching Creating Custom Thesaurus Entries in SQL Server 2005 and 2008 Full Text Search Creating and Customizing Noise Words in SQL Server 2005 Full Text Search Creating and Customizing Noise Words / StopWords in SQL Server 2008 Full Text Search Advanced Queries for Using SQL Server 2008 Full Text Search StopWords / StopLists [...]

  3. Marios Philippopoulos Says:

    How can one get which column(s) a full-text index is defined on? There is the unique_index_id field of the sys.fulltext_indexes catalog view, but this points to the unique index (eg. PK) used for defining the full-text index. There is no column in that catalog view pointing to the actual column in the table on which the ft index is defined.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 93 other followers

%d bloggers like this: