Getting Started with SQL Server 2005 Full Text Searching: Part 2 – The Indexes

Yesterday I introduced you to full text searching, and covered the basics on creating catalogs to hold your full text indexes. A full text search index is a little different than a regular index. First, each table can only have one full text search index created for it. Next, the create syntax is slightly different. OK, in fact it’s a lot different. Let’s take a look:

create fulltext index on my_table_name_here
(column1, column2,…)
key index my_tables_unique_index_name
on my_catalog_name_here
with change_tracking {manual | auto | off}, no population

The first thing is also the most obvious, you need to supply the name of the table in the first line. Note we’re not supplying a name for the full text search index. Since there’s only one per table, SQL Server takes care of creating the full text search index name for us.

Next we need to supply the name of the column or columns we want indexed. These can be any sort of text field. Just list them one after another, separated by commas.

The next item is also required, and sort of tricky. Each row in the table you are doing full text searching on must have a unique index. It makes sense when you think about it, for the text search to be efficient it must be able to quickly move to the row with the word you’re hunting for, and the way to do that is via the unique index.

So for this parameter you’ll need to supply a unique index name for “my_tables_unique_index_name”. Keep in mind this is not the name of the columns from the table. Instead this is the name of a “normal” index (not a full text search index) that is unique for the table.

The “on” parameter is optional, you only need it if you set up multiple catalogs and don’t have a default. If you omit it, it will simply put the new index in the default catalog.

Next you will need to tell SQL Server how often to update the index. You do this through the with change_tracking parameter. OFF turns it off entirely, no updates will be done until you issue a rebuild via the alter syntax I’ll cover momentarily. You might want to use OFF when you have a table that gets updated very rarely.

AUTO, on the other hand is for when you have a table that gets updated frequently. It will update the full text search index when the associated table is updated. The final option, MANUAL will flag changes to the underlying table, but it won’t update the full text search index until you tell it to.

The final parameter, no population, only applies when you use OFF. It tells SQL Server not to populate the index when it’s created. If you omit it, or use AUTO or MANUAL, SQL Server will populate the full text search index when the index is created.

OK, so you’ve got this index created and need to change it, or perhaps you need to work with one that’s already in existence. For this there’s the alter command:

alter fulltext index on my_table_name_here
parameters here

There’s quite a few parameters you can pass, so let’s look at them individually. Just know that when you see them below, they should go where you see “parameters here” above.

set change_tracking {off | auto | manual} – This works the same as with the create command, it lets you change the tracking mode.

disable – Disables the full text search index, it’s not used for searching nor is it updated. However the data is left intact, should you want to turn it back on.

enable – Enables the full text search index after a disable.

add ( column ) – Adds the passed in column to the full text search index.

drop ( column ) – Removes the passed in column from the full text search index.

start full population –This rebuilds the index from the ground up.

start incremental population –This will update the index since the last time it was updated. Note you must have a timestamp column on your table for this to work.

start update population –Remember a moment ago when I talked about the change_tracking manual option? Well this command is how you update an index with manual change tracking.

And finally, you may decide one day you no longer need the full text search index. Since the readers of this blog are the smartest, most intelligent readers on the planet you’ve already figured out we’ll need to use a variant of the drop command:

drop fulltext index on my_table_name_here

And there you go, you now know how to create, change, or remove a full text search index. Now there’s one more piece, you need to know how to use them from within your SQL. But we’ll save that for tomorrow.