Creating Full-Text Catalogs and Indexes - SQL Server 2008

SQL Server 2008 iFTS allows you to efficiently search character data stored in char, nchar, and nvarchar columns. It can also be used to search binary content in varbinary columns, and XML data in xml datatype columns.

Before you can take advantage of iFTS’s powerful search capabilities, you need to create a full-text catalog and one or more full-text indexes. You can create these either through SQL Server Management Studio or by using Transact-SQL (T-SQL) statements.

Using the GUI to Create a Full-Text Catalog and Index

Using Management Studio, you can easily create full-text catalogs and full-text indexes. Here, we’ll walk through creating a full-text catalog for the AdventureWorks database and a full-text index on the Production.ProductModel table.

Creating a Full-Text Catalog

To create the full-text catalog, in Object Explorer, expand the Storage node under the target database, right-click the Full Text Catalogs node, and select New Full-Text Catalog from the context menu, as shown in Figure.

Choosing to create a new full-text catalog

Choosing to create a new full-text catalog
You’ll be presented with the New Full-Text Catalog dialog box. In this dialog box, you select a name for your new full-text catalog, the full-text catalog owner, an accent-sensitivity setting, and whether this full-text catalog will be designated your default full-text catalog. The accent-sensitivity setting determines whether or not iFTS differentiates between characters with and without accent marks. Figure shows the entries for creating a catalog named AdvFTCatalog for this example.

Creating a new full-text catalog

Creating a new full-text catalog

Once you’ve created your full-text catalog, it’s time to define one or more full-text indexes on tables in your database.

Creating a Full-Text Index

Management Studio includes a Full-Text Indexing Wizard to guide you through creating a full-text index. Follow these steps to use the wizard:

  1. Right-click a table in Object Explorer and select Full-Text Index ➤Define Full-Text Index.
  2. Choosing to define a full-text index

    Choosing to define a full-text index

  3. The welcome screen for the SQL Server Full-Text Indexing Wizard appears, as shown in Figure. Click Next to continue.
  4. The Full-Text Indexing Wizard asks you to choose a single-column unique index from your source table, as shown in Figure. This index provides iFTS with a means of relating fulltext index entries back to rows in the source table. Very often, you will see an integer primary key, usually a surrogate key, used as the index of choice for iFTS. In this example, we’ve used the integer primary key of the table, PK_ProductModel_ProductModelID.
  5. Starting the Full-Text Indexing Wizard

    Starting the Full-Text Indexing Wizard

    Selecting an index in the Full-Text Indexing Wizard

    Selecting an index in the Full-Text Indexing Wizard

  6. The wizard next asks you to select the table columns to index. You can add any characterbased, binary, or large object (LOB) datatype columns to your full-text index. In Figure, we’ve chosen to add the CatalogDescription, Instructions, and Name columns of the table to the full-text index. These particular columns hold XML data and character-type data.
  7. Selecting columns for a full-text index

    Selecting columns for a full-text index

  8. The next step of the wizard gives you a choice of full-text index change-tracking options, as shown in Figure. The default is automatic change tracking, which causes your full-text index to automatically update whenever data in the underlying table is changed. If you have a very large table and expect many changes to the underlying data, automatic updates might use a lot of system resources during peak production times. If this is the case, you might decide to kick off updates manually or on a regular schedule instead of automatically after every data update. For our example, the default automatic change tracking works well.
  9. The wizard next allows you to choose the full-text catalog in which to create your full-text index, as shown in Figure. You can choose an existing catalog or create a new one. You can also choose the filegroup on which to create the full-text index and the full-text stoplist to use with the full-text index. Stoplists are composed of stopwords, which are words considered unimportant for purposes of full-text searches—words like the, an, and, and to. Stopwords are ignored during full-text queries. We’ll discuss stoplists in greater detail in the “Using Stoplists” section later in this chapter.
  10. Selecting automatic change tracking for the full-text index

    Selecting automatic change tracking for the full-text index

    Assigning a full-text index to a full-text catalog

    Assigning a full-text index to a full-text catalog

  11. Optionally, you can define population schedules for tables and full-text catalogs, as shown in Figure. This option is useful if you’ve decided not to use automatic full-text index change tracking and want to schedule updates for off-peak times, or if you just want finergrained control over the full-text index population process. Since we’ve gone with the default automatic change tracking in the example, we don’t need to define a population schedule.
  12. Optionally, you can set a full-text index population schedule.

    Optionally, you can set a full-text index population schedule.

  13. The final step of the Full-Text Indexing Wizard is a summary screen that allows you to review all the options you selected as you worked your way through the wizard’s screens. This is strictly an informational screen, but it provides you the option to go back to previous screens if you made a mistake or want to make a change before your full-text index is created. Clicking Finish on this screen creates your full-text index in the database. Figure shows the Full-Text Indexing Wizard summary screen produced by the example.

Full-Text Indexing Wizard summary screen

Full-Text Indexing Wizard summary screen

After you click Finish in the Full-Text Indexing Wizard, a progress screen is displayed, showing the success or failure of the full-text index creation process. Figure shows that the full-text index in the example was successfully created.

Successful full-text index creation

Successful full-text index creation

Using T-SQL to Create a Full-Text Catalog and Index

It’s often necessary to script full-text catalog and full-text index creation. There are a number of reasons to script your full-text catalog and index creation, including the following:

  • Many organizational information technology policies require database changes to be checked into a source-control database to maintain proper source code management and version control.
  • Many database administrators (DBAs) demand that database creation and updates be scripted so they can be propagated uniformly across multiple servers or promoted consistently through different environments like development, quality assurance (QA), user acceptance testing (UAT), and production.
  • You may just be the type of person who prefers the fine-grained control that T-SQL code gives you.

To create the same full-text index as in the example in the previous section, use the CREATE FULLTEXT CATALOG statement:

CREATE FULLTEXT CATALOG AdvFTCatalog
ON FILEGROUP [PRIMARY]
WITH ACCENT_SENSITIVITY = ON
AUTHORIZATION [dbo];

The CREATE FULLTEXT CATALOG statement specifies the full-text catalog name, which must be unique among full-text catalog names in the current database. This example specifies that the fulltext catalog will be created on the PRIMARY filegroup, accent sensitivity will be turned on, and the owner is dbo (via the AUTHORIZATION clause). We create the full-text catalog on the PRIMARY filegroup, since the AdventureWorks database has only a single filegroup by default. In production environments, Microsoft recommends creating an additional filegroup just for full-text catalogs.

After you create the full-text catalog with T-SQL you need to create one or more full-text indexes on it. The CREATE FULLTEXT INDEX statement fills this need:

CREATE FULLTEXT INDEX ON Production.ProductModel
(
Name LANGUAGE 1033,
CatalogDescription LANGUAGE 1033,
Instructions LANGUAGE 1033
)
KEY INDEX PK_ProductModel_ProductModelID
ON (AdvFTCatalog)
WITH
(
CHANGE_TRACKING AUTO,
STOPLIST = SYSTEM
);
GO
ALTER FULLTEXT INDEX ON Production.ProductModel
ENABLE;
GO

As in the Full-Text Indexing Wizard example in the previous section, this CREATE FULLTEXT INDEX statement creates a full-text index on the Name, CatalogDescription, and Instructions columns of the Production.ProductModel table. The single-column unique index used by the fulltext index is specified in the mandatory KEY INDEX clause. As in the previous example, we’ve used the integer primary key of the table, PK_ Product Model_ Product ModelID.

Each column in the example has the optional LANGUAGE identifier included, specifying a locale identifier (LCID) of 1033, which indicates US English. We’ve also specified automatic change tracking and usage of the default system stoplist.

The ALTER FULLTEXT INDEX statement enables the full-text index we just created on the Production.ProductModel table. Additional full-text index management functionality is exposed by the ALTER FULLTEXT INDEX and DROP FULLTEXT INDEX statements. You can use ALTER FULLTEXT INDEX to enable or disable a full-text index; set the change-tracking mode for a full-text index; change the stoplist used by a full-text index; and start, stop, pause, or resume a full-text index population. Thefollowing code starts a full population of the Production.ProductModel table’s full-text index.

ALTER FULLTEXT INDEX ON Production.ProductModel
START FULL POPULATION;
GO

One you’ve created your full-text catalog and full-text indexes, you can take advantage of them with SQL Server’s full-text search predicates and functions.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

SQL Server 2008 Topics