Full-text search allows extensive word searches of textual data, and it is an additional component that you can add to the SQL Server installation. (For details, see the section titled "Adding Components and Instances" in Chapter 2.) After you install the full-text search component, you can manage full-text searches by using the methods described in the following sections.

Several administration tasks are required to utilize full-text search. You must:

Real World

You can use CREATE FULLTEXT CATALOG to create full-text catalogs. Be sure to use the IN PATH clause to set the file location. Because the file may be searched frequently, you probably will want to have the catalog file on its own drive, and the IN PATH clause lets you specify the file location.

Before you create a searchable full-text index from a catalog, you must ensure the table or view has a unique, single-column, non-nullable index. The full-text search engine uses this unique index to map rows in the tables to unique keys. You can create a unique index using CREATE UNIQUE INDEX. The syntax is:

CREATE UNIQUE INDEX indexname on table(uniquecolumn)

The following example creates an index called ui_per on Employee table in the Personnel database using the EmpID column:

use Personnel

go

CREATE UNIQUE INDEX ui_per on dbo.Employee(EmpID)

go

Once you have a unique key, you can create a full-text index on the database table using CREATE FULLTEXT INDEX or Object Explorer view.

When you create a full-text catalog, the search service (msftesql) creates full-text indexes of textual data contained in the database. Then the service manages the indexes and provides the primary mechanism for examining the data they contain. Unlike previous versions of the search service, the search service is managed separately for each instance of SQL Server. There is, however, only one search service per server. The search service runs under the same service account as the related SQL Server service. Thus, if you change the service account for the SQL Server service, you must change the service account for the search service as well.

Note

In the Support Services folder in SQL Server Management Studio, the Microsoft Search service is referred to as Full-Text Search. Don’t let the terminology confuse you. Both references are to the same service, and the correct name is Microsoft Search service.

The concept of a full-text index in SQL Server may be a bit different than you have encountered elsewhere. In SQL Server, a full-text index stores information about keywords and their location within a specific column. These text indexes are created for each table in the database, and groups of indexes are contained in catalogs. Each full-text catalog is treated as a file, and it is included in the database file set to facilitate backup and restore utilities so that you can back up and restore full-text catalogs like other SQL Server data, eliminating the need to repopulate catalogs completely after a database restore. The BACKUP and RESTORE statements in Transact-SQL can be used to back up and restore full-text catalogs with a database automatically. For backup and recovery options, see Chapter 14.

Note

Full-text catalogs are also attached and detached with the database, so full-text catalogs are preserved when you move databases to new locations. You no longer have to delete and rebuild the full-text catalog. You simply detach the database, copy it to a new location, and then reattach it. The full-text catalog is preserved.

The all-table column text is indexed, including the columns containing the new XML data type. This allows you to perform full-text searches on column values that contain XML data as well as any other type of textual data. In earlier versions of SQL Server, you could query linked servers, but you could not perform full-text queries against linked servers. When searching tables in previous versions, you were limited to searching one column or all columns, but now you can search any number of columns. New features in SQL Server 2005 allow you to issue full-text queries referencing a remote linked server and to search across multiple columns.

Full-text indexes are defined on base tables and views that have one or more full-text indexed base tables. They are not defined on views, system tables, or temporary tables. The ability to search on views is an important new change for SQL Server 2005. Indexes are populated with key values that have information about the significant words in a table, such as the column they are in and their location in the column. You can create, modify, and implement full-text catalogs and indexes using both stored procedures and data definition language (DDL) statements. In Transact-SQL, you can test rows against a full-text search condition using the functions contains and freetext. You can also return the set of rows that match a full-text search condition using the functions containstable and freetexttable.