Home > Articles > Databases > SQL Server

SQL Server Reference Guide

< Back Page 85 of 361 Next >

Full-Text Indexes

Last updated Mar 28, 2003.

Full-Text searches give you a broader range of text to search with, usually in char or varchar fields. They're used in an optional SQL Server construct called a full-text search. Once you've installed this feature, you can search on complex string functions within a query. And, if you've stored complex binary objects (such as Microsoft Word documents or Excel Spreadsheets) in a field, you can search within that document as if it were a SQL field.

To use full text searches, you need the Microsoft Search service installed on the operating system on which the SQL Server is running. This service only runs on Windows Server 2000 and higher. Microsoft Search is the search engine used when a Full-Text query is called from SQL Server.

To install the service, stick in the SQL Server CD, and add the feature under Add Server Components. With that done, make sure you re-apply whatever service pack level your server is currently running.

With the service installed, you use either Enterprise Manager or a set of stored procedures to create and manage indexes for a column. Unlike regular indexes, you can only have one Full-Text index per table, and only 256 in the entire database. Another difference is that the indexes aren't stored in the database, but on the OS's file system, which is why you need a set of stored procedures to create and manage them. That's the process I'll focus on in this tutorial. I'll cover the use of Full-Text searches more completely in another tutorial.

You can use graphical or command-line tools to create and implement Full-Text Indexes. I'll start with Enterprise Manager to demonstrate an index creation. The classic example is indexing the notes field in the titles table. Whenever you see a "notes" style field, it's usually a good candidate for a Full-Text index. There are some string tricks you can do to get the same result, but a Full-Text Index is often a good way to go, especially if you have binary data stored, such as a Microsoft Word file in a field.

Moving to the pubs database, I drill down to the titles field. Once I right-click the titles table, I select Full-Text Index Table... and then Define Full-Text Indexing on a Table..., as shown below:

Figure 144

This brings up a wizard. In the first screen, it asks me to define a unique index on the table. Microsoft Search uses this index to tie the items it stores in the index back to the table. There's only one unique index on this table, so I select that, and then select the notes field on which to create the full text index.

The next step asks me to provide the name and location of the index file:

Figure 145

If this were a production system, I'd be sure to separate this file location from the indexes, logs, data files, and tempdb spindles.

The last step of the wizard allows me to set a schedule for the population and re-indexing of the Full-Text Index. I don't set a schedule here, since I'll do everything manually. If you do set a schedule, make sure you take the load into account. Don't let it interfere with your normal maintenance.

With the index created, I need to populate the entries. To do that, I drill down to the table again, and right-click it. I then select the menu item called Full-Text Index Table and then Start Full Population. Once that's complete, I can use Full-Text Search. To try that, I open Query Analyzer and type in the following query:

USE PUBS
GO
SELECT title, notes
FROM titles
WHERE CONTAINS (notes, ' "how to use" ')
GO

And here's the result:

title                   notes                                                 
-------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
Emotional Security: A New Algorithm            Protecting yourself and your loved ones from undue emotional stress in the modern world. Use of computer and nutritional aids emphasized.
Cooking with Computers: Surreptitious Balance Sheets        Helpful hints on how to use your electronic resources to the best advantage.

(2 row(s) affected)

Beyond having to maintain these indexes manually, there are a few other important things to keep in mind. If you lose the drive where the catalogs exist, then you'll need to re-create them from scratch. The drive where these indexes and catalogs are located also have to be local to SQL Server, not a shared drive. And, you'll have to create the indexes for each table to be able to use them.

There are three stored procedures that you can use instead of the graphical methods I showed you here. To create the same index, you could use the following procedures:

-- First, enable the database for Full-Text Indexes:
EXEC sp_fulltext_database 'enable'

-- Next, reference the table, create the index, name it, and set the unique index:
EXEC sp_fulltext_table 'titles', 'create', 'TitleNotes', 'UPKCL_titleidind'

-- Add the column:
EXEC sp_fulltext_column 'titles','notes','add'

-- Now Activate the index:
EXEC sp_fulltext_table 'titles','activate'

-- And finally populate them:
EXEC sp_fulltext_table 'titles',' start_full'

Working with Full-Text Indexes is a useful way to give your applications the flexibility to search in text fields or binary documents, but they do have a cost. Making a call out to the operating system to find the file isn't the best use of resources, and it costs extra in I/O. On the other hand, it isn't often that the users search this way, and it's a good idea to give your apps as much flexibility as you can.