ALTER FULLTEXT CATALOG (Transact-SQL)
Changes the properties of a full-text catalog.
.gif "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 from the file system and a new catalog is created in its place. Rebuilding does not change the full-text metadata in the database system tables.For REBUILD to succeed, the FILEGROUP the catalog resides in must be online, or read-writeable. After the rebuild, the full-text index will be repopulated.
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 indexes 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. The FILEGROUP that the full-text catalog resides on, and the FILEGROUP or FILEGROUPs that the full-text indexed table or tables reside on, must not be OFFLINE or READONLY for the command to succeed.
Keep in mind that, depending on the amount of indexed data, a master merge may take some time to complete.
- 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
CREATE FULLTEXT CATALOG (Transact-SQL)
DROP FULLTEXT CATALOG (Transact-SQL)