How to: Define Full-text Catalogs

  • Article

You can use full-text searches to find data that you cannot find by using a simple LIKE clause. By performing a full-text search, you can find strings based on the rules of a particular language. In contrast, the LIKE clause works only on specific character patterns. For example, a full-text search for the string "dog-house" would find references to both "dog house" and "dog-house." For more information about full-text searches, see this page on the Microsoft Web site: Full-text Search.

Before you can perform a full-text search on a particular column, you must define a full-text catalog on a particular filegroup. You must also define a full-text index on the particular column that you want to search. You can define different catalogs on different filegroups if, for example, you want to back up and restore the catalog together with the other files in that filegroup. You might also define catalogs on different filegroups to associate them with different physical disk drives so that you can increase performance.

Before you can define a full-text catalog on a filegroup other than the primary filegroup, you must first define the filegroup. For more information, see How to: Add Files and Filegroups. To specify where the full-text catalog will reside, you must use the IN PATH RootPath clause. If you do not specify a location, the full-text catalog is created in the default directory for that database.

Note

If you are working with a Data-tier Application (DAC) project, you cannot define full-text catalogs. You must use a database project if you want to use full-text catalogs in your database.

To define a full-text catalog

  1. If Schema View is not displayed, open the View menu, and click Database Schema View.

  2. In Schema View, expand the database project in which you want to define the full-text catalog.

  3. Expand the Storage folder, right-click the Full Text Catalogs folder, point to Add, and click Full Text Catalog.

    The Add New Item dialog box appears with the Full Text Catalog template highlighted.

  4. In Name, type the name that you want to give the catalog.

  5. Click Add.

    The full-text catalog is added to the database project. The Transact-SQL editor opens, displaying the definition for the full-text catalog.

  6. Modify the definition for the full-text catalog.

    If you want to create the full-text catalog on the default filegroup, your catalog definition should resemble the following example:

    CREATE FULLTEXT CATALOG CatalogName

    If you want to create the full-text catalog on a secondary filegroup, your catalog definition should instead resemble the following example:

    CREATE FULLTEXT CATALOG CatalogName
ON FILEGROUP FilegroupName

    For more information about the syntax for the Transact-SQL statement, see this page on the Microsoft Web site: CREATE FULLTEXT CATALOG (Transact-SQL).

  7. On the File menu, click Save CatalogName**.fulltext.sql** to save your changes to the definition.

    You can now define full-text indexes in the new full-text catalog.

See Also

Tasks

How to: Create a Database Object

How to: Modify Database Objects

How to: Build a Database Project to Generate a Compiled Schema (.dbschema) File

Concepts

Overview of Files and Filegroups