How do I create a full text search catalog for my SQL Server database?

Open the Enterprise Manager for SQL Server and navigate to your SQL Server database (in the left side navigation pane, select a SQL Server Group; next, select a registered SQL Server, and finally select under the "Databases" entry your SQL Server database). You see a number of entries under your database; one of them is called "Full Text Catalogs." This shows you any full text search catalog defined for your database. Right-click on the right side pane or the "Full Text Catalogs" item and select "New Full-Text Catalog" from the popup menu. Give the full text search catalog a name and select where the catalog files get placed which is by default "c:\Program Files\Microsoft SQL Server\MSSQL\FTData". The "Schedules" tab allows you to set one or more schedules when the full text search catalog gets updated; for example, perform a full population every Sunday and an incremental population every 10 minutes.

Click on the "New Catalog Schedule" button to define a new schedule. Give the schedule a name and select whether it is enabled or disabled through the "Enabled" check box. Under the job type, select whether this schedule performs a full population or incremental population. A full population rebuilds the search catalog and an incremental population indexes changes only since the last population. Finally, you select the schedule, which can be either at startup time of the SQL Server Agent, a specific date and time, or a recurring schedule. If you select "Recurring," click on the "Change" button to define the recurrence schedule; for example, every 10 minutes or every day at 1:00 AM from July 1st to July 31st.

How do I enable full text searching on one or more fields of a table?

To perform a full text search on a table, you need to create a full text index on the table. You can only define one full text index per table. Select the "Table" entry in the left side navigation pane to get a list of all tables defined in the database. Right-click on a table and select "Full-Text Index Table | Define Full-Text Indexing on a Table" from the popup menu. This will bring up a wizard that allows you to select which fields should be indexed. Tables that are indexed need to have a "unique single column index" that does not allow Nulls; for example, a primary key on an ID field that does not allow Nulls or an index on an ID field that has a unique constraint and does not allow Nulls. As the index gets built, each index entry points back to the table rows it applies to (through the unique identifier). SQL Server delegates any free text search as part of a query to the Microsoft Search service. The Search service performs the actual free text search and then returns the list of rows to include in the result-set. This is done through the unique identifier associated with each index entry.

The "Full-Text Indexing" wizard guides you through the process of creating a full text index. First, you select the "unique index" to use that allows you to select from a list of all unique indexes present on the table. Next, you select the fields to index by checking the checkbox in front of each field to index. The list only shows fields that can be indexed that are fields of the following data types: char, nchar, varchar, nvarchar, text, ntext, and image. All data types except the image data type are treated as text fields; therefore, the TXT IFilter will be used during the indexing process. Fields of the image data type contain file images. When you select a field of the data type image then you select under "document type column" which field will contain the data type of the file stored in the image field. The Search service looks at this field during the indexing process to understand the file type stored and which IFilter to apply. For example, you may have a field called FileImage of the data type image and a field called FileType of the data type nvarchar. While creating records in the table, you would store the file in the FileImage field and the file type in the FileType field; for example, "zip".

After selecting all the fields to index, you select to which full text catalog this index belongs. You can select from the list of existing catalogs or create a new one. Next, you can add a new catalog schedule or table index schedule. Any catalog schedule you add will apply to all table indexes in the full text catalog. A table index schedule you add will apply only for the table index that allows you to create different schedules for different tables. Finishing the wizard will apply all the changes, meaning it will add the catalog schedules, create the table index, and create the table index schedules.

How do I manage existing full-text catalogs?

SQL Server provides a number of options to manage your full-text catalogs. The "Full-Text Catalogs" entry will list all defined catalogs. In the right side pane, right-click on the catalog to manage. The popup menu will show you a number of options:

• Rebuild Catalog: Rebuilds the catalog that generates a new empty catalog
• Start Full Population: Starts a full population, which effectively rebuilds the catalog
• Start Incremental Population: Indexes all changes since the last population
• Stop Population: Stops a running population
• Schedules: Brings up the list of defined schedules and allows you to change the existing schedules or create new ones
• Delete: Allows you to delete the catalog with all its table indexes
• Properties: Shows the properties of the catalog; the "Tables" tab shows all the tables that have an index and are part of this catalog; the "Schedules" tab" shows all the schedules defined.

How do I manage table indexes?

You can find out through the full-text catalogs which tables have a table index defined. You then find the appropriate table and right-click on it. From the "Full-Text Index Table" popup menu, you can select from a number of options:

• Edit Full-Text Indexing: Brings up the "Full-Text Indexing" wizard that allows you to edit the full text index
• Remove Full-Text Indexing from a Table: Allows you to remove a table index
• Start Full Population: Starts a full population, which effectively rebuilds the table index
• Start Incremental Population: Indexes all changes since the last population
• Stop Population: Stops a running population
• Schedules: Brings up the list of defined schedules and allows you to change the existing schedules or create new ones.

The attached sample database

The attached SQL Server database named "FullTextSearchSample" illustrates how you can store files in a database and search the file contents through the full-text search engine from SQL Server. It contains a table called DocumentLibrary that has a field DocumentImage of type image and DocumentType of type nvarchar. The attached sample application "Insert Files into Database" provides a Windows forms application to insert files into a table. You enter the name of the database server and the user credentials to use. Next, you enter the name of the table where to insert the file into, the name of the field where to insert the file contents, and the name of the field where to insert the file extension. Finally, you select the file to insert and click "Insert" to create a new record in the table and insert the file and file type. You also can achieve this by using the TextCopy utility provided by SQL Server. It is located at "c:\Program Files\Microsoft SQL Server\MSSQL\Binn". Here is an example:

TextCopy /S servername /U username /P password
         /D FullTextSearchSample /T DocumentLibrary
         /C DocumentImage /I /F filename /W "where ID = 8" /z