Each database that you want to search must have its own full-text catalog. When you create a catalog, you can set a schedule for populating the catalog on a regular basis, or you elect to manually populate the catalog as necessary. Populating the catalog updates the full-text indexes for the catalog and ensures that the search results are accurate. SQL Server supports several methods for populating catalogs including:
Full population. The search service builds index entries for all rows in all the tables or views covered by the full-text catalog. In most cases, you perform a full population only when you create a catalog or need to refresh the entire contents of a catalog.
Incremental population. The search service only changes index entries for rows that have been added, deleted, or modified since the last population. You can perform incremental population only on tables or views that have a time-stamp column. If a table or view does not have a time-stamp column, full populations are always performed.
Update population. The search service uses update population in conjunction with change tracking. Change tracking of tables and views allows full-text search to track changes in the catalog or views and update the catalog either automatically or manually. If you disable change tracking, you can perform incremental population only on tables or views that have a time-stamp column (and if a table or view does not have a time-stamp column, full populations are always performed).
Creating a catalog is only one part of the indexing process. After you create the catalog, you must select individual tables or views for indexing and associate these with the catalog. You also need to specify the individual table or view columns that should be indexed. Periodically, you may also need to clean up old catalogs.
Catalogs are stored separately from the databases themselves. You can set the catalog file location when you create the catalog. You cannot change or view the file location after the catalog is created. To examine other catalog configuration properties on a database for which you have configured full-text search, complete the following steps:
Start SQL Server Management Studio, and then connect to a server of your choice.
Use Object Explorer view to access a database. Expand the server node and the Databases node.
Under the Databases node, expand Storage and Full Text Catalogs nodes to display the catalog(s) for the selected database (see Figure 5-9).
Right-click the catalog, and then choose Properties to open the Full-Text Catalog Properties dialog box shown in Figure 5-10.
The General tab provides the following information:
Default Catalog. Indicates if the catalog is the default for the database.
Population Status. Indicates whether the catalog is being built or updated (referred to as populating).
Name. Specifies the name of the catalog.
Last Populate Date. Specifies the date the catalog was last populated. For new catalogs, this will be blank.
Item Count. Specifies the number of items cataloged.
Catalog Size. Specifies the total size of the catalog on disk.
Owner. Specifies the catalog owner. If no owner was set when the catalog was created, the owner is set as dbo.
Filegroup. Specifies the filegroup with which the catalog is associated. Whenever the associated filegroup is backed up or restored, the catalog will be backed up or restored as well.
Unique Key Count. Specifies the number of unique keys cataloged.
Accent Sensitive. Indicates if the catalog is accent sensitive. The default sensitivity is set the same as the database.
You need catalogs to perform full-text searches of databases. A single database can have multiple catalogs associated with it, and you can use these catalogs to perform different types of searches. For example, in a customer database, you could create one catalog for searching company contact information and another for searching account history.
You cannot create full-text catalogs in the master, model, or tempdb databases. Although a full-text catalog can have several full-text indexes, a full-text index can only be part of one full-text catalog. To create a catalog for a database, complete the following steps:
Start SQL Server Management Studio, and then connect to a server.
Use Object Explorer view to access a database. Expand the server node and the Databases node.
Under the Databases node, expand Storage and Full Text Catalogs nodes to display the catalog(s) for the selected database.
Right-click Full-Text Catalogs, and then select New Full-Text Catalog to display the New Full-Text Catalog dialog box shown in Figure 5-11.
Type a descriptive name for the catalog in the Full-Text Catalog Name text box.
Use the Catalog Location box to set the physical file location of the catalog. You can type the folder path or click the location button (. . .) to choose a folder path.
Note
Catalogs are considered to be a type of database file, and they are associated with a specific filegroup. You can set the location of the catalog only when you create the catalog. After the location is set, the value cannot be changed without re-creating the catalog. If catalogs will be searched frequently, you should plan where they will be located carefully. Catalog searches are read intensive. Data is written to catalogs when catalogs are updated. Also, note that a catalog’s location is no longer listed in the standard properties, so you should document the location of each catalog.
The catalog is backed up and restored as part of a designated filegroup. Use the Filegroup box to associate the catalog with a specific database filegroup.
Use the Owner box to set the catalog owner. The default catalog owner is dbo. If you do not want to use the default, type a new user or database role name in the text box provided or click the object button (. . .) to browse for a user or database role to use.
If you want to make the catalog the default for the selected database, select Set As Default Catalog.
By default, catalog accent sensitivity is set the same as the selected database. If you want to allow accent-sensitive searches, select the Sensitive option. Otherwise, select Insensitive to allow searches to match characters without regard to accents.
Click OK to create the catalog.
Next, you will need to enable indexing and populate the catalog.
You can enable indexing of a table or view by completing the following steps:
Start SQL Server Management Studio, and then connect to a server.
Use Object Explorer view to access a database. Expand the server node and the Databases node.
Under the Databases node, expand Tables or Views as appropriate. Right-click the table or view, point to Full-Text Index, and then select Define Full-Text Index to start the Full-Text Indexing Wizard. Click Next.
Select a unique index for the table or view, and then click Next. The index is used as a unique constraint on a single column in the table and can be used in joins. If the table or view does not have a unique index, you must exit the wizard, create an index (as discussed in the section titled "Working with Full-Text Search" earlier in this chapter), and then restart this process.
As shown in Figure 5-12, select the character or image-based columns that you want to index. Each column can be set with a language constraint that identifies the natural language of the column. With text and binary data, you can also specify a document type. Click Next.
Specify if you want the changes to the tables and views to be tracked for the purpose of updating the full-text catalog. When you make a decision about automatically tracking changes, keep the following information in mind:
If you elect to automatically track changes, the wizard will fully populate the catalog with the index for this table before exiting. The catalog will also be configured so that changes are tracked as they occur, and the changes will be applied automatically to keep the catalog up to date with regard to the current table or view.
If you elect to manually track changes, the wizard will fully populate the catalog with the index for this table before exiting. Then you can apply tracked changes manually to keep the catalog up to date with regard to the current table or view.
If you do not want to track changes and do not want the catalog to be populated at this time, select Do Not Track Changes and clear Start Full Population When Index Is Created.
Click Next. Use the Select Full-Text Catalog drop-down list to choose an existing catalog, or select Create A New Catalog, and then configure the new catalog options.
Click Next. You can now select or create population schedules for the catalog. You can also select or create a population schedule for the currently selected table.
Real World
In most cases, you will want to create schedules for populating an entire catalog rather than an individual table. However, in some cases, populating individual tables makes sense, especially if the contents of a particular table change frequently and contents of other tables change rarely.
Click Next, and then click Finish. SQL Server Management Studio defines the full-text index for the table. If you selected the Do Not Track Changes option and cleared Start Full Population When Index Is Created, you must populate the index manually or create a schedule for performing this task. Otherwise, the index is populated in the catalog.
To change the indexing settings of a table, complete the following steps:
Start SQL Server Management Studio, and then connect to a server.
Use Object Explorer view to access a database. Expand the server node and the Databases node.
Under the Databases node, expand Tables or Views as appropriate. Right-click the table or view, point to Full-Text Index, and then select Properties to display the Full-Text Indexing Properties dialog box for the table or view. Then use the dialog box options to edit the settings you want to change.
The Full-Text Indexing Properties dialog box has three pages:
General. Shows the current configuration. Properties in bold can be changed, and properties that are dimmed cannot be changed. Select or clear the Full-Text Indexing Enabled option to enable or disable indexing. Use the Change Tracking option to configure manual or automatic change tracking, or to turn change tracking off.
Columns. Shows the unique index and index columns. You can change the unique index and the index column settings using the options provided.
Schedule. Shows currently defined schedules for populating the full-text index. You can add and modify schedules using the options provided.
If you want to stop full-text indexing of a table or view temporarily, you can disable the related full-text index. It is useful to disable full-text indexing if you are making a lot of updates to a table or view, to avoid triggering an update, or to prevent a scheduled incremental or full population of the database.
If you decide that you do not want to index a table or view any more, you can remove full-text indexing completely. Removing full-text indexing removes all references to the table or view in the related catalog. This data must be recreated if you later decide to restore full-text indexing.
To disable or remove full-text indexing from a table or view, complete the following steps:
Start SQL Server Management Studio, and then connect to a server.
Use Object Explorer view to access a database. Expand the server node and the Databases node.
Under the Databases node, expand Tables or Views as appropriate. You can now:
Disable the full-text index. To do so, right-click the table or view, point to Full-Text Index, and then select Disable Full-Text Index.
Remove the full-text index. To do so, right-click the table or view, point to Full-Text Index, and then select Remove Full-Text Index. When prompted to confirm the action, click OK.
After you select the tables and views you want to index, you can fully populate the catalog so that the table or view can be searched in connection with the related catalog. After the catalog is populated with this data, you can maintain the catalog related to a table or view manually, according to a schedule, or through change tracking. With scheduled jobs, you set a schedule that instructs SQL Server Agent to run a one-time or recurring job that populates the catalog. You can populate catalogs at the database level or at the table level. In most cases, you will want to create schedules for populating an entire catalog rather than an individual table. However, there are times when populating individual tables makes sense, especially if the contents of a particular table change frequently and the contents of other tables change rarely.
To populate a catalog manually with text for all tables and views selected for indexing, complete the following steps:
Start SQL Server Management Studio, and then connect to a server.
Use Object Explorer view to access a database. Expand the server node and the Databases node.
Under the Databases node, expand Storage and Full Text Catalogs nodes to display the catalog(s) for the selected database.
Right-click Full Text Catalogs, and then select Rebuild All to rebuild the entire full-text index. To rebuild only a catalog of interest, right-click the catalog and select Rebuild.
To set a schedule for populating a catalog with text for all tables and views selected for indexing, complete the following steps:
Start SQL Server Management Studio, and then connect to a server.
Use Object Explorer view to access a database. Expand the server node and the Databases node.
Under the Databases node, expand Storage and Full Text Catalogs nodes to display the catalog(s) for the selected database.
Right-click the catalog you want to work with, and then select Properties to display the Full-Text Catalog Properties dialog box.
Select the Population Schedule page under Select A Page. Any currently scheduled jobs are listed by name and population type. You can edit or delete existing jobs, or click New to create a new schedule for populating the catalog.
In the New Full-Text Indexing Catalog Schedule dialog box, type a descriptive name for the job used to schedule the catalog population.
Set the schedule type. Typically, you will want to select either One Time or Recurring.
Use the remaining options to determine when the job runs. One-time jobs run at a specific date and time. Recurring jobs run daily, weekly, or monthly at a specific date and time.
Tip
In most cases, you will want to create a recurring job. The first time the job is run, it will perform a full population of the catalog if this task has not been performed previously. On successive runs, the job will perform the population according to the population type. SQL Server Agent runs scheduled tasks. The agent identifies scheduled tasks by the unique job name you specify. You will learn more about SQL Server Agent in Chapter 15.
Click OK to close the New Full-Text Indexing Catalog Schedule dialog box.
In the Full-Text Catalog Properties dialog box, click in the Population Type column for the scheduled job you just created. The options depend on the state of the catalog and include:
Catalog – Full. Performs a full population of the catalog, which essentially rebuilds the catalog. This is a task that you will select only occasionally. For example, you might rebuild a catalog once a quarter.
Catalog – Incremental. Performs an incremental population of the catalog based on time-stamp changes. With change tracking, an incremental build updates the catalog for tracked changes as well. You can also apply tracked changes manually.
Catalog – Optimize. Performs an optimize build of the catalog to improve performance. This is a task you might want to schedule periodically to enhance search performance. However, optimization, like a rebuild, can be a time-consuming and resource-intensive process, so you will want to schedule this task when activity levels are low.
Click OK.
To manually populate a catalog with text for a single table or view, complete the following steps:
Start SQL Server Management Studio, and then connect to a server.
Use Object Explorer view to access a database. Expand the server node and the Databases node.
Under the Databases node, expand Tables or Views as appropriate. Right-click the table or view, point to Full-Text Index, and then select Start Full Population or Start Incremental Population. If you want to stop the population, right-click the table or view, select to Full-Text Index, and then select Stop Population.
To set a schedule for populating a catalog with text for a single table, complete the following steps:
Start SQL Server Management Studio, and then connect to a server.
Use Object Explorer view to access a database. Expand the server node and the Databases node.
Under the Databases node, expand Tables or Views as appropriate. Right-click the table or view, point to Full-Text Index, and then select Properties.
Select the Schedules page under Select A Page. Any current scheduled jobs are listed by name and population type. You can edit or delete existing jobs, or click New to create a new schedule for populating the catalog with text for the table or index.
In the New Full-Text Indexing Table Schedule dialog box, type a descriptive name for the job used to schedule the catalog population.
Set the schedule type. Typically, you will want to use One Time or Recurring.
Use the remaining options to determine when the job runs. One-time jobs run at a specific date and time. Recurring jobs run daily, weekly, or monthly at a specific date and time.
Note
In most cases, you will want to create a recurring job. The first time the job is run, it will perform a full population of the catalog if this task has not been performed previously. On successive runs, the job will perform the population according to the population type. SQL Server Agent runs scheduled tasks. The agent identifies scheduled tasks by the unique job name you specify. You will learn more about SQL Server Agent in Chapter 15.
Click OK to close the New Full-Text Indexing Table Schedule dialog box.
In the Full-Text Index Properties dialog box, click in the Population Type column for the scheduled job you just created. The options are:
Table – Full. Performs a full population of the catalog, which essentially rebuilds the index for the table in the catalog. This is a task that you will select only occasionally.
Table – Incremental. Performs an incremental population of the catalog based on time-stamp changes to the table or index.
Table – Update. Performs an update population of the catalog based on tracked changes. You can also apply tracked changes manually.
Click OK.
When you make frequent changes to a database, catalogs can sometimes become inconsistent with the contents of a database. Over an extended period of time, catalogs also can become quite large. To resynchronize the catalog with the contents of the database or to compress a large catalog so that it does not waste space, you must rebuild the catalog.
You can rebuild catalogs individually or you can rebuild all the catalogs used by a database. To rebuild a single catalog, complete the following steps:
Start SQL Server Management Studio, and then connect to a server.
Use Object Explorer view to access a database. Expand the server node and the Databases node.
Under the Databases node, expand Storage and Full Text Catalogs nodes to display the catalog(s) for the selected database.
Right-click the catalog, and then select Rebuild. When prompted to confirm the action, click OK.
Use the Rebuild Full-Text Catalog dialog box to track the rebuild status, stop the rebuild, and display a rebuild report. Note any errors or warnings and read the related details in the report carefully. Click Close.
To rebuild all catalogs associated with a database, complete the following steps:
Start SQL Server Management Studio, and then connect to a server.
Use Object Explorer view to access a database. Expand the server node and the Databases node.
Under the Databases node, expand Storage and Full Text Catalogs nodes to display the catalog(s) for the selected database.
Right-click Full Text Catalogs, and then select Rebuild All. When prompted to confirm the action, click OK.
Use the Rebuild All Full-Text Catalog dialog box to track the rebuild status, stop the rebuild, and display a rebuild report. Note any errors or warnings and read the related details in the report carefully. Click Close.
Although the full-text search components do a good job of maintaining indexes and cleaning up after themselves, you will want to monitor the number and size of catalog files. You should also regularly clean up old catalogs to eliminate wasted storage space. You do this by completing the following steps:
Start SQL Server Management Studio, and then connect to a server.
Use Object Explorer view to access a database. Expand the server node and the Databases node.
Under the Databases node, expand Storage and Full Text Catalogs nodes to display the catalog(s) for the selected database.
Right-click the catalog, and then select Properties to display the Full-Text Catalog Properties dialog box.
On the General page, select Optimize Catalog, and then click OK.
To remove a catalog, complete the following steps:
Start SQL Server Management Studio, and then connect to a server.
Use Object Explorer view to access a database. Expand the server node and the Databases node.
Under the Databases node, expand Storage and Full Text Catalogs nodes to display the catalog(s) for the selected database.
Right-click the catalog, and then select Delete. Click OK in the Delete Object dialog box.
To remove all catalogs associated with a database, complete the following steps:
Start SQL Server Management Studio, and then connect to a server.
Use Object Explorer view to access a database. Expand the server node and the Databases node.
Under the Databases node, expand Storage. Right-click Full Text Catalogs, and then select Delete All. When prompted to confirm the action, click OK.