Getting Started with SQL Server 2005 Full Text Searching: Part 1 – The Catalog

One of the coolest features of SQL Server 2005 is the ease with which you can implement full text searching. True, it was available in previous versions but 2005 makes it very easy to implement and use.

Full Text Search is an offshoot of the Microsoft Index Server technology. It’s what you could call an “add-on”. By default it’s enabled for every database you create in 2005.

But just having it turned on is not enough, now you have to create a catalog to hold the data for your full text data. The catalog is a separate file from your database, and holds all the key words it finds. The syntax to create a catalog is pretty simple:

create fulltext catalog my_catalog_name_here
in path ‘c:\mysqldata\somesubdirectory’
as default

The ‘in path’ is optional, if you omit it your catalog is created in the same place as the data. For small databases this is fine, for large ones you might actually want to store the catalog on a separate hard disk in order to get a performance boost.

The ‘as default’ clause says this catalog will be the default one used for new full text search indexes, or for searching existing ones. Most times you’ll probably only need one catalog for a database, so you can add this and forget it.

Once you have a catalog created, you may need to tweak it. There’s not a lot of tweaking you can do, just three ways you can alter it, and all are implemented via the alter command.

alter fulltext catalog my_catalog_name_here rebuild

alter fulltext catalog my_catalog_name_here reorganize

alter fulltext catalog my_catalog_name_here as default

The first command, rebuild does just what it says. Your old catalog goes to the great bit bucket in the sky (i.e. it’s deleted) and SQL Server will recreate all of your full text search indexes. And it should be obvious, but remember during this time your full text search will not be available.

Reorganize is something like doing a disk defrag, it cleans up and reorganizes your full text search indexes. While it may not be as efficient as doing a complete rebuild, it does have the advantage of not taking the catalog offline while it does it’s work.

Finally ‘as default’ simply makes the catalog the default, in case you either forgot or were distracted by Mike Rowe doing something nauseating on “Dirty Jobs” (http://www.discovery.com/dirtyjobs) .

OK, you now have a catalog. But the catalog is simply a space to hold your full text search indexes, and those we’ll create in the next post.

About these ads

2 Responses to “Getting Started with SQL Server 2005 Full Text Searching: Part 1 – The Catalog”

  1. Creating and Customizing Noise Words in SQL Server 2005 Full Text Search « Arcane Code Says:

    [...] Now for the bad news. (Hey, you didn’t think it was going to be that easy did you?) In order for the noise word file to take effect you must repopulate the full text indexes. You can do the entire catalog at once, or one table at a time. To do the entire catalog, use the alter full text catalog command. For more info on this command, see my post on Getting Started with SQL Server 2005 Full Text Searching: Part 1 – The Catalog. [...]

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

    [...] 0 – Getting the Bits to do Full Text Searching in SQL Server 2005 Lesson 1 – The Catalog Lesson 2 – The Indexes Lesson 3 – Using SQL Lesson 4 – Valid Data Types Lesson 5 – Advanced [...]


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 102 other followers

%d bloggers like this: