Managing Full-Text Indexing

Managing Full-Text Indexing

             Full-text indexes store information about significant words and their location within the columns of a database table. In SQL Server 2012, the Full-Text Engine is part of the SQL Server

process rather than a separate service. Only one full-text index can be created per table or

indexed view. A full-text index can contain up to 1,024 columns.

To create a full-text index by using SQL Server Management Studio, perform the following steps:

1. Right-click the table on which you want to create a new full-text index and choose

Design.

2. From the Table Designer menu, click Full-Text Index to open the Full-Text Index dialog

box. Click Add. Configure the properties of the index.

You can use the CREATE FULLTEXT INDEX statement to create a full-text index on a table.

For example, to create a FULLTEXT index on the Production.ProductReview table in the

AdventureWorks2012 database by using the ReviewerName, EmailAddress, and Comments

columns in the existing unique key index PK_ProductReview_ProductReviewID while also creating

a FULLTEXT catalog called production_catalog, use the following statement:

USE AdventureWorks2012;

GO

CREATE FULLTEXT CATALOG production_catalog;

GO

CREATE FULLTEXT INDEX ON Production.ProductReview(ReviewerName, EmailAddress, Comments)

KEY INDEX PK_ProductReview_ProductReviewID

ON production_catalog;

GO

To delete a full-text index, right-click the table that hosts the full-text index, choose Full-

Text Index, and then select Delete Full-Text Index. You can also delete a full-text index by

using the DROP FULLTEXT INDEX statement. For example, to drop the index created in the

previous example, use the query:

DROP FULLTEXT INDEX ON Production.ProductReview