Setting up Full Text Search with SQL Express 2005 and Management Studio Express
Here’s one for the .NET/MSSQL/SQL Express crowd.
Management Studio Express is a nice interface for managing SQL Express databases. While I prefer Visual Studio for working with the data and stored procedures, MSE is essential for the more high level maintenance like database creation and attaching, managing users, and setting up Full Text Search indexes.
First, a brief primer on FTS. It’s a brilliant tool that will take a lot of the effort out of writing search queries. It allows for much more than just boolean searches. It can also search for inflectional variations. For instance, if you searched for “apply” it will also return results for “applying,” “applies,” and “applied.” It can also return search score information to help you rank results — to which you can also apply custom weighting. The first thing to know, though, is that it doesn’t come with the default SQL Express 2005 installation! You need to install SQL Express 2005 with Advanced Services in order to have access to FTS at all.
Once you’ve got it installed in order to get it working you need to:
- enable FTS on the database you want to use
- create a folder to store the FTS index (optional: it will use the default folder if you don’t specify a location, but if you do specify a location the folder has to already exist)
- create a fulltext catalog with a unique ID, optionally set to the path created in the previous step
- add the catalog to each table in the database that will utilize it
- specify the columns in the tables that will use the FTS data
- specify the language of each column (optional, but if you don’t set this inflectional won’t work)
The first two steps are easy. In MSE, just right click on the database you want to add FTS to and open the Properties dialog. Open the Files page, and check the box next to “Use full-text indexing.” Save that and you’re ready to go. If you’re going to specify the location for the index, create the folder using whatever tool you fancy.
The first problem you’re going to run into, though is actually creating the FTS catalog in the first place. This is something that you can’t do in MSE. At least, I’ve never been able to find out how! Fortunately there’s a handy command line tool called SSEUtil (from Microsoft). It’s a bit more user friendly than the standard sqlcmd utility, which you could also use.
When starting sseutil from the command line, give it the “-c” (console) and “-m” (main) arguments. That gives you a command line into the same databases you see listed in MSE. Don’t omit the “-m” or you are actually starting sseutil as a secondary process and you will probably not have access the database you’re trying to edit. Don’t ask me why, I have no idea what the mechanics are here. All I know is that it usually takes me four hours of banging my head on the desk before I remember that little “-m” and then suddenly everything works. If you can access your database with Windows Security, that’s all you need.
sseutil -c -m
Once in sseutil, you just run two very simple commands. Don’t forget you have to say “go” to commit each command before processing. (I’m sure you could run them both and say “go” once, but it’s nice to get confirmation at each point.)
This selects the database you are managing, so replace “db_name” with the right name. To list the databases available use the sp_databases command. (Don’t forget the “go.”) Once you have confirmation “Command completed successfully” you just need to run the sp_fulltext_catalog stored procedure. This is where you give the catalog a unique ID and the optional path. You also need to tell it that you’re wanting to create a catalog (the same sp is used for deleting or re-indexing a catalog).
Again this is using sample names so replace “dbname_fulltext” and “c:\fts\dbname” with whatever works for you.
After you get confirmation on this, you’re finished with the command line. Close sseutil and get back to MSE. Open the drop down list for the database you’re managing, and open Tables. Find the table you want to add FTS to, right click and select Modify. This will open the column editing page.
In order for FTS to work, the table has to have a unique primary key. If the table doesn’t have one yet, create one. Now right click on the key icon in the margin next to the primary key, and select “Full-text Index”. The first step is to add this table to the FTS catalog you just created. This is as easy as clicking on the “Add” button (which isn’t available if you haven’t successfully created a catalog on this database!), which will add the table using the default name of FullText_for_tablename.
Now you will see the properties box on the right fill up. The only properties that you need to worry about right now is the very top one, under General. Click on that, and you’ll notice an […] button appear on the far right of that field. Click this button to add the columns of the table to the index.
This interface is very easy, just select from the available columns in the dropdown box on the first cell. The datatype will automatically be added. The “Typed by Column” box can be ignored, but I recommend selecting the language from the Language cell. This will allow you to do inflectional searches — without knowing the language SQL can’t apply the inflectional variations.
And that’s it! Add each column that you want, and do this for every table that you want. The index itself will be created and updated automatically.
For further reference, here is Microsoft’s page detailing Full-Text Search concepts. I recommend starting with Simple Term searches, and then looking into the inflectional searches. The primary SQL commands (technically, two ‘predicates’ and two ‘functions’) that are used in FTS queries are CONTAINS, FREETEXT, CONTAINSTABLE, and FREETEXTTABLE. Good luck!