Share via

ALTER FULLTEXT CATALOG (Transact-SQL)

  • Article

Changes the properties of a full-text catalog.

Topic link icon Transact-SQL Syntax Conventions

Syntax

ALTER FULLTEXT CATALOG catalog_name 
{ REBUILD [ WITH ACCENT_SENSITIVITY = { ON | OFF } ]
| REORGANIZE
| AS DEFAULT 
}

Arguments

  • catalog_name
    Specifies the name of the catalog to be modified. If a catalog with the specified name does not exist, Microsoft SQL Server returns an error and does not perform the ALTER operation.

  • REBUILD
    Tells SQL Server to rebuild the entire catalog. When a catalog is rebuilt, the existing catalog is deleted and a new catalog is created in its place. All the tables that have full-text indexing references are associated with the new catalog. Rebuilding resets the full-text metadata in the database system tables. 

  • WITH ACCENT_SENSITIVITY = {ON|OFF}
    Specifies if the catalog to be altered is accent-sensitive or accent-insensitive for full-text indexing and querying.

    To determine the current accent-sensitivity property setting of a full-text catalog, use the FULLTEXTCATALOGPROPERTY function with the accentsensitivity property value against catalog_name. If the function returns '1', the full-text catalog is accent sensitive; if the function returns '0', the catalog is not accent sensitive.

    The catalog and database default accent sensitivity are the same.

  • REORGANIZE
    Tells SQL Server to perform a master merge, which involves merging the smaller indexes created in the process of indexing into one large index. Merging the full-text index fragments can improve performance and free up disk and memory resources. If there are frequent changes to the full-text catalog, use this command periodically to reorganize the full-text catalog.

    REORGANIZE also optimizes internal index and catalog structures.

    Keep in mind that, depending on the amount of indexed data, a master merge may take some time to complete. Master merging a large amount of data can create a long running transaction, delaying truncation of the transaction log during checkpoint. In this case, the transaction log might grow significantly under the full recovery model. As a best practice, ensure that your transaction log contains sufficient space for a long-running transaction before reorganizing a large full-text index in a database that uses the full recovery model. For more information, see Manage the Size of the Transaction Log File.

  • AS DEFAULT
    Specifies that this catalog is the default catalog. When full-text indexes are created with no specified catalogs, the default catalog is used. If there is an existing default full-text catalog, setting this catalog AS DEFAULT will override the existing default.

Permissions

User must have ALTER permission on the full-text catalog, or be a member of the db_owner, db_ddladmin fixed database roles, or sysadmin fixed server role.

Note

To use ALTER FULLTEXT CATALOG AS DEFAULT, the user must have ALTER permission on the full-text catalog and CREATE FULLTEXT CATALOG permission on the database.

Examples

The following example changes the accentsensitivity property of the default full-text catalog ftCatalog, which is accent sensitive.

--Change to accent insensitive
USE AdventureWorks;
GO
ALTER FULLTEXT CATALOG ftCatalog 
REBUILD WITH ACCENT_SENSITIVITY=OFF;
GO
-- Check Accentsensitivity
SELECT FULLTEXTCATALOGPROPERTY('ftCatalog', 'accentsensitivity');
GO
--Returned 0, which means the catalog is not accent sensitive.

See Also

Reference

sys.fulltext_catalogs (Transact-SQL)

CREATE FULLTEXT CATALOG (Transact-SQL)

DROP FULLTEXT CATALOG (Transact-SQL)

Concepts

Full-Text Search (SQL Server)