Share via

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 Full-text Search (https://go.microsoft.com/fwlink/?LinkId=85722).

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.

To define a full-text catalog

  1. If Schema View is not displayed, open the View menu, and click 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 (T-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 T-SQL statement, see CREATE FULLTEXT CATALOG (Transact-SQL) (https://go.microsoft.com/fwlink/?LinkId=85722).

  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: Prepare Database Build Scripts

Concepts

Terminology Overview of Team Edition for Database Professionals

Other Resources

Overview of Files and Filegroups