CREATE FULLTEXT CATALOG (Transact-SQL)
Creates a full-text catalog for a database. One full-text catalog can have several full-text indexes, but a full-text index can only be part of one full-text catalog. Each database can contain zero or more full-text catalogs.
You cannot create full-text catalogs in the master, model, or tempdb databases.
.gif "Topic link icon")
Transact-SQL Syntax Conventions
Syntax
CREATE FULLTEXT CATALOG catalog_name
[ON FILEGROUP filegroup ]
[IN PATH 'rootpath']
[WITH <catalog_option>]
[AS DEFAULT]
[AUTHORIZATION owner_name ]
<catalog_option>::=
ACCENT_SENSITIVITY = {ON|OFF}
Arguments
catalog_name
Is the name of the new catalog. The catalog name must be unique among all catalog names in the current database. Also, the name of the file that corresponds to the full-text catalog (see ON FILEGROUP) must be unique among all files in the database. If the name of the catalog is already used for another catalog in the database, SQL Server returns an error.The length of the catalog name cannot exceed 120 characters.
ON FILEGROUP 'filegroup'
Is the name of the SQL Server filegroup of which the new catalog will be part. If filegroup is not specified, the new catalog will be part of the default filegroup used for all full-text catalogs. The default full-text filegroup is the primary filegroup for the database. We recommend that full-text catalogs be put on a secondary filegroup. This lets you do filegroup backup-and-restore operations of full-text catalogs independent of the data and log files. Additionally, by putting the full-text catalogs in a secondary filegroup, you can restore an online full-text catalog.The filegroup that the full-text catalog is created on must contain at least one available data file, which is used for internal structures that are part of the full-text catalog. This file, stored in SQL Server, must not be marked OFFLINE or READONLY.
Catalogs in a filegroup are treated like files, and their physical locations are indicated by the path in sys.master_files. The name of the file that is created for each full-text catalog is the combination of sysft_ and the catalog name. For example, if the catalog name is catname, the name of the file that corresponds to that catalog would be sysft_catname.
IN PATH 'rootpath'
Is the root directory for the catalog. If rootpath is not specified, the new catalog will be located in the default directory specified at setup.Full-text catalogs must be created on a local hard disk drive associated with an instance of SQL Server. rootpath cannot be a relative path; it must reside on a drive on the same computer. Although you can specify the root of a drive (for example, 'C:\') as the location of a full-text catalog, we do not recommend it. Network drives, removable drives, floppy disks, and Universal Naming Convention (UNC) paths are not supported. In a failover cluster environment, the path must be on a shared disk on which the SQL Server resource depends.
If an existing full-text catalog name is specified with an incorrect path, SQL Server returns an error and does not create the full-text catalog. If the specified rootpath does not exist, SQL Server returns an error stating that the path does not exist.
To make differential backup, restore, and recovery of full-text catalogs possible, the full-text catalog must be stored in a directory that is part of an NTFS file system. Differential backup, restore, and recovery are not supported on file systems that are not NTFS.
The full-text catalog will be created as a directory in the root path specified; the directory name will be the name of the full-text catalog. If a directory with the same name as the catalog already exists, a suffix is appended to the name of the catalog and a new directory is created that has that name.
Do not create full-text catalogs in the root of a drive, such as c:\.
ACCENT_SENSITIVITY = {ON|OFF}
Specifies that the catalog is accent sensitive or accent insensitive for full-text indexing. When this property is changed, the index must be rebuilt. The default is to use the accent-sensitivity specified in the database collation. To display the database collation, use the sys.databases catalog view.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 value returned is '1', the full-text catalog is accent sensitive; if the value is '0', the catalog is not accent-sensitive.
- AS DEFAULT
Specifies that the catalog is the default catalog. When full-text indexes are created without a full-text catalog explicitly specified, the default catalog is used. If an existing full-text catalog is already marked AS DEFAULT, setting this new catalog AS DEFAULT will make this catalog the default full-text catalog.
AUTHORIZATION owner_name
Sets the owner of the full-text catalog to the name of a database user or role. If owner_name is a role, the role must be the name of a role that the current user is a member of, or the user running the statement must be the database owner or system administrator.If owner_name is a user name, the user name must be one of the following:
- The name of the user running the statement.
- The name of a user that the user executing the command has impersonate permissions for.
- Or, the user executing the command must be the database owner or system administrator.
owner_name must also be granted TAKE OWNERSHIP permission on the specified full-text catalog.
Permissions
User must have CREATE FULLTEXT CATALOG permission on the database, or be a member of the db_owner, or db_ddladmin fixed database roles.
Examples
The following example creates a full-text catalog and also a full-text index.
USE AdventureWorks;
GO
CREATE FULLTEXT CATALOG ftCatalog AS DEFAULT;
GO
CREATE FULLTEXT INDEX ON HumanResources.JobCandidate(Resume) KEY INDEX PK_JobCandidate_JobCandidateID;
GO
See Also
Reference
ALTER FULLTEXT CATALOG (Transact-SQL)
DROP FULLTEXT CATALOG (Transact-SQL)
Other Resources
Full-Text Search
New Full-Text Catalog (General Page)