title | description | author | ms.author | ms.reviewer | ms.date | ms.service | ms.subservice | ms.topic | f1_keywords | helpviewer_keywords | monikerRange | ||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Create and Manage Full-Text Catalogs |
Create and Manage Full-Text Catalogs |
rwestMSFT |
randolphwest |
mikeray |
03/14/2017 |
sql |
search |
conceptual |
|
|
=azuresqldb-current||>=sql-server-2016||>=sql-server-linux-2017||=azuresqldb-mi-current |
[!INCLUDE SQL Server Azure SQL Database Azure SQL Managed Instance] A full-text catalog is a logical container for a group of full-text indexes. You have to create a full-text catalog before you can create a full-text index.
A full-text catalog is a virtual object that does not belong to any filegroup.
Use CREATE FULLTEXT CATALOG. For example:
USE AdventureWorks;
GO
CREATE FULLTEXT CATALOG ftCatalog AS DEFAULT;
GO
-
In Object Explorer, expand the server, expand Databases, and expand the database in which you want to create the full-text catalog.
-
Expand Storage, and then right-click Full Text Catalogs.
-
Select New Full-Text Catalog.
-
In the New Full-Text Catalog dialog box, specify the information for the catalog that you are re-creating. For more information, see New Full-Text Catalog (General Page).
[!NOTE]
Full-text catalog IDs begin at 00005 and are incremented by one for each new catalog created. -
Select OK.
Use the [!INCLUDEtsql] function FULLTEXTCATALOGPROPERTY to get the value of various properties related to full-text catalogs. For more info, see FULLTEXTCATALOGPROPERTY.
For example, run the following query to get the count of indexes in the full-text catalog Catalog1
.
USE <database>;
GO
SELECT fulltextcatalogproperty('Catalog1', 'ItemCount');
GO
The following table lists the properties that are related to full-text catalogs. This information may be useful for administering and troubleshooting full-text search.
Property | Description |
---|---|
AccentSensitivity | Accent-sensitivity setting. |
ImportStatus | Whether the full-text catalog is being imported. |
IndexSize | Size of the full-text catalog in megabytes (MB). |
ItemCount | Number of full-text indexed items currently in the full-text catalog. |
MergeStatus | Whether a master merge is in progress. |
PopulateCompletionAge | Difference in seconds between the completion of the last full-text index population and 01/01/1990 00:00:00. |
PopulateStatus | Populate status. [!INCLUDEssNoteDepFutureAvoid] |
UniqueKeyCount | Number of unique keys in the full-text catalog. |
Run the Transact-SQL statement ALTER FULLTEXT CATALOG ... REBUILD, or do the following things in SQL Server Management Studio (SSMS).
-
In SSMS, in Object Explorer, expand the server, expand Databases, and then expand the database that contains the full-text catalog that you want to rebuild.
-
Expand Storage, and then expand Full Text Catalogs.
-
Right-click the name of the full-text catalog that you want to rebuild, and select Rebuild.
-
To the question Do you want to delete the full-text catalog and rebuild it?, click OK.
-
In the Rebuild Full-Text Catalog dialog box, click Close.
-
In SSMS, in Object Explorer, expand the server, expand Databases, and then expand the database that contains the full-text catalogs that you want to rebuild.
-
Expand Storage, and then right-click Full Text Catalogs.
-
Select Rebuild All.
-
To the question, Do you want to delete all full-text catalogs and rebuild them?, click OK.
-
In the Rebuild All Full-Text Catalogs dialog box, click Close.
Run the Transact-SQL statement DROP FULLTEXT CATALOG, or do the following things in SQL Server Management Studio (SSMS).
-
In SSMS, in Object Explorer, expand the server, expand Databases, and expand the database that contains the full-text catalog you want to remove.
-
Expand Storage, and expand Full Text Catalogs.
-
Right-click the full-text catalog that you want to remove, and then select Delete.
-
In the Delete Objects dialog box, click OK.