Implementing Full-Text Indexes


In my previous blog we saw the Full-Text Index architecture.

In this blog we shall see its implementation. Before you proceed you need to ensure that this feature is added to your instance and the full-text services are running.

We shall start by creating a full text catalog. If multiple filegroups are available we may specify the one on which it has to be created by the FileGroup clause If multiple filegroups are available we may specify the one on which it has to be created by the FileGroup clause

Through T-SQL









Through Wizard

Next we shall create the Full Text Index on our books table


( [Author] LANGUAGE English,

  [Title] LANGUAGE English,

  [Synopsis] LANGUAGE English


KEY INDEX [pk_id] ON ([FullTextCatalog])



Steps for creating the Index via the Wizard are as follows

Unlike Standard indexes we aren’t giving it any name. As SQL server allows only one Full-Text index per table the naming is taken care by SQL server. The Key Index clause specifies the unique non-null column which is mandatory when create full-text indexes.

In the previous blog I’ve mentioned how population of the index depends on the type of Change_Tracking option selected.
When Auto is selected the changes are automatically tracked and updated by SQL server for the full-text indexed columns. 
When Manual is select the changes are tracked but the indexes aren’t updated unless the ALTER FULLTEXT INDEX  START UPDATE POPULATION is fired. This may be called through a SQL agent job as well. There is one more option which is not commonly used. 
It’s the Change_Tracking Off option when the changes are not tracked at all by SQL server. It’s available when creating full-text indexes in static tables.

Here im creating my own StopList. This option is available under Database-> Storage -> Full Text StopList. I shall be building it from the system stop list and adding the word “describes” to StopList.

Now we are ready with the index let’s start the querying

So coming back to the same example that we discussed in the previous blog I can now search for all the books that contain the character “Jeremy” in the synopsis as follows

SELECT * FROM [FULLTEXT].[dbo].[Books] where contains(Synopsis,N'Jeremy')

The Contains Predicate will search for the exact words or phrases that I pass. It also searches for proximity words & inflecting forms of the word. If you want more matches in terms of meaning of the words and not just the exact words we use the FreeText Predicate. It also allows for pattern matching with wildcards.

SELECT * FROM [FULLTEXT].[dbo].[Books] where freetext(*,'"new York*"')

This will result for all those records will also contain the records like new Yorker, new york in any of the columns

I could go on for providing further examples but these are very well described in the below links. 

Highly recommend that you go through these examples prior to implementing it in queries.