Last week we looked at how to search for data by passing a simple search word into a SQL Query that uses Full Text Searching. As you would expect, Full Text Searching is capable of much more powerful searches than a single word.

First, you can search for a phrase. To do so, you include the phrase inside double quotes.

SELECT WebSiteID, WebSiteName
FROM MyTableOfCoolWebsites
WHERE CONTAINS(*, ‘”Arcane Code”’)

Will find occurances of the phrase Arcane Code in your index. Let’s say however, you want to search for either Arcane or Code? FTS supports Boolean searches.

WHERE CONTAINS(*, ‘”Arcane” OR “Code”’)

Will return results where either the word Arcane or Code is in the text. If you want them both, use an AND in place of the OR. Additionally, FTS supports the AND NOT keyword, for when you want the first word but not the second.

Full Text Search also supports something called Proximity searches. In a proximity seach, one word appears near another.

WHERE CONTAINS(*, ‘arcane near code’)

So how near is near? Well the online docs are a bit vague on this, a safe guess is about ten words. You may also see this form of the syntax:

WHERE CONTAINS(*, ‘arcane ~ code’)

~ equates to near, although to me not nearly as readable, be aware though in case you run across it.

Finally, you should be aware that certain words are excluded from searches. Common words such as a, an, the, and so on. Microsoft refers to these as “Noise Words”. You can edit the list of noise words in case you have some words in your environment that wind up being Noise Words. Your company name might be one example.

I found the file in the folder C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\FTData (your milage may vary). The file is named noiseenu.txt. (ENU is for English US, not to be confused with noiseeng.txt which our friends in the British Isles will be using.)

This is also handy to know in case there is a reserverd word you need to remove from the list. In our environment one of the reserve words is also an abbreviation for a piece of our equipment, so I would want to remove this from our list.

Hopefully everyone had some fun over the weekend with their digital cameras and listening to some good photo podcasts. Today we’ll return to the subject of Full Text Searching (FTS) under SQL Server 2005.

If you’re like me one of your questions is “OK, FTS is great, but exactly what data can I index with it?” Good question, a quck reference of the books online gives us the answer.

Char, nchar, varchar, nvarchar, text, ntext, xml, varbinary(max), and image are the valid data types for FTS. However, according to the documentation text, ntext, and image are going to be removed in a future version of SQL Server, so I’m going to avoid them and so should you.

Char, nchar, varchar, nvarchar are pretty straight forward, and probably expected, so I won’t go into them further. XML is pretty cool, as you may be aware with SQL Server 2005 Microsoft added the ability to store XML in the database. You can then do XML queries into these XML fields, a subject for a future post. However, MS also allows you to search inside these XML fields using Full Text Searching as well.

The final one that needs explanation then is varbinary(max). I’m sure you’re scratching you’re head over this, how can FTS work on binary data? Well remember, the Full Text Search engine is built on top of the Microsoft Indexing Service, which can scan inside Word, Excel, etc files stores on a server.

Thus FTS can actually look inside files stored inside varbinary(max) and if they have the proper extension FTS will look inside them and index the contents of the files. How sweet is that?

OK, so you have this spiffy catalog, and you’ve populated it with full text searches for your favorite tables. Now you’d like to actually use those index from within your SQL. There are four new commands you can use in SQL to get to your data. All are used as part of the where clause, and have similar syntax but different results.

The first is the one you’ll probably use the most, it’s the contains command. You simply pass in the column name and what you want to search for.

select col1, col2 from myTable
where contains(fts_column, ‘searchword’)

For fts_column you can use the name of one of the columns that was indexed, or you can use * (and asterisk) to search in all of the columns that were full text search indexed. In the single quotes you put in the word or phrase you want to look for.

Contains searchs for an exact match. It either finds it or it doesn’t, and it has to be an independent word. For example, if your text field contained “I love the Mythbusters every week.” and you searched for ‘Mythbuster’, would NOT return a match.

If you want your text searching to be a little more open minded, use the freetext command instead. The syntax is identical to contains, including the ability to use an asterisk.

select col1, col2 from myTable
where freetext(fts_column, ‘searchword’)

In this case, however, a search of our afore mentioned text field for ‘Mythbuster’ would return a match, as freetext understands that Mythbuster and Mythbusters are essentially the same word.

In your application, you might consider using a check box that says “exact match”. For exact match queires use the contains keyword, when the user does not check you can use the freetext command.

It’s also possible to return a list of results that are sorted by a rank. The rank indicates the strength of the match to the search phrase passed in. To get a list of ranks, use either the containstable or freetexttable commands. Their syntax is like their cousins, as is the method it uses for searching (containstable is exact, freetexttable is more liberal). The only addition is the first parameter must be the name of the table, then comes the column name and search condition.

Instead of rows, what is returned are two columns: key and rank. The rank is a relative score from 0 to 1000 that indicates the strength of the match. A higher value means it’s a better match.

The key is the primary key from the table you’re searching. You can then use this key to pull back the data from the main table. Let’s do a simple example: you want an exact match for all employees who live in Alabama. Unfortunately the DBA who created the table had just come off a three day drinking binge, and instead of separate street / city / state fields, just created a big text field called emp_address.

select rank, emp_id, emp_name from empTable
join freetexttable(empTable, emp_address, ‘Alabama’) ftt
on empTable.emp_id = ftt.[KEY]

This would return something like:

Rank emp_id emp_name
255 12345 Jamie Hyneman
128 45678 Adam Savage

And there you go, four ways you can have your SQL leverage the power of full text searching to return results.

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.

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.