Since SQL Server 7, Microsoft has linked the capabilities of the Microsoft Search service with their database product in order to perform word and phrase matching more efficiently in character and text fields. Using LIKE within a WHERE clause can be expensive, especially when searching large text fields, so most database developers use other means to search database fields, usually employing costly search engines that catalog the data and that work outside the normal SQL interfaces of the existing database. To solve this problem, SQL Server now offers direct access to the existing Search service, which can be accessed through any SQL Server interface.
This article discusses configuring and maintaining SQL Server search catalogs, covering the following topics:
Designing new full-text catalogs
Setting Up the Catalogs
The Microsoft Search service is a separate service from the database that runs independently of the SQL service. It builds its own full-text catalogs in separate files. Each catalog can be responsible for from one to many tables, but a table can only belong to one catalog. Every catalogued table must have a primary key. If incremental populations occur, the table must include a column with the timestamp datatype. Any number of char, varchar, and text columns per table can be indexed.
Building the Catalog
There are several ways to build catalogs. The easiest way is to use the Enterprise Manager (EM). As you navigate down the EM tree, a particular database will list Tables, Views, Stored Procedures, Full-Text Catalogs, and so on. Right-click the Full-Text Catalogs listing in the EM tree to display a list of options, including the option to create a new catalog. When you select this option, a catalog wizard opens in typical Microsoft fashion, and it walks you through configuring the catalog. Note that this method only creates the catalog file (and sets up a population schedule if designated). The user still must add tables and columns associated with the particular catalog.
Another alternative for creating catalogs is to use the stored procedure sp_fulltext_catalog. This procedure allows creation, rebuilding, dropping, and populating of a catalog.
Populating the Catalog
After the catalog is created, a table or set of tables can be attached to the catalog. Using Enterprise Manager, display the list of tables. Right-click the desired table and select the Full-Text Index option. If the table has not yet been defined, select Define Full-Text Index. After the table has been defined, other options allow population, rebuilding, dropping, and so on. Separate schedules for individual table population within the same catalog can also be defined.
There are three options in populating text catalogs:
Perform a full population. If the catalog is already populated, it empties the results and starts fresh with the existing data in the table(s). This is not an ideal solution for catalogs that are hit throughout the day, or for very large tables.
Perform a change-tracking population. After the initial population, change tracking updates the catalog any time a row is inserted, updated, or deleted. This is ideal for tables that are being hit with transactions while requiring searching at the same time.
Incremental population. This option is good for catalogs that are responsible for tables that are periodically bulk-loaded. The method requires a column with the timestamp datatype; otherwise, it will perform a full population.
Setting the population schedule is an easy task that can be accomplished through a couple of different methods. You can manually fire up the population process whenever the mood strikes you. This isn't the best strategy, since it will most likely cause the catalog to be out of date with your data. But generally, when you first set up the catalog, the initial population will be done manually. To do this, you can either right-click the catalog under Full-Text Catalogs in EM, or right-click the table that's part of a catalog. Either method presents a list of options; Start Full Population and Start Incremental population will be listed, along with the option Schedules. To set up or edit a schedule, click the Schedules option. If you edit an existing schedule, options include specifying the population type and when the population will occur. Set these according to the needs of the catalog and of the application that will use the catalog.