Professional SQL Server™ 2005 Programming

21.2. Setting Up Full-Text Indexes and Catalogs

As we saw in the last section, each table in a SQL Server table can have zero or one full-text indexes. These full-text indexes are stored in a file — called a full-text catalog — that is external to the SQL Server database and system files. A catalog can store multiple full-text indexes. The indexes must be from the same database; you may, however, want to store indexes from one database in multiple catalogs, so you can manage the population of those indexes on separate schedules.

21.2.1. Enabling Full-Text for Your Database

For SQL Server 2005, full-text indexing is turned on by default for every database you create. In previous versions, however, SQL Server would not allow full text catalogs or indexes to be created until you enabled full-text search for the database in question. Keep this in mind if you're working with an earlier version of SQL Server.

To check whether full-text indexing is on or off, you make use of the DATABASEPROPERTYEX system function. Just write a simple query to call the function:

SELECT DATABASEPROPERTYEX('AdventureWorks', 'IsFulltextEnabled')

This returns a 1 if full text is enabled and a 0 if it is not. (See Appendix A for more information on what DATABASEPROPERTYEX can obtain information on.)

In the event that you do need to enable (or disable for that matter) full-text indexing on your database, you make use of a special system stored procedure called sp_fulltext_database. The syntax is amazingly ...