Use the Full-Text Indexing Wizard
Updated: August 19, 2016
Applies To: SQL Server 2016
The Full-Text Indexing Wizard in SSMS walks you through a series of steps designed to help you create a full-text index.
In Object Explorer, right-click the table on which you want to create a full-text index, point to Full-Text index, and then click Define Full-Text Index. This action launches the Wizard in a separate window. Click Next
Unique Index. Select an index from the drop down list. The index must be a single-key-column, unique, non-nullable index. Select the smallest unique key index for the full-text unique key. For best performance, a clustered index is recommended.
Available Columns. Check the box next to all column names for columns you want to include. check box next to the column name. Ineligible columns are greyed out and their check boxes disabled.
Language for Word Breaker. Select a language from the drop-down list. This choice will be used to identify the correct word breakers for the index. SQL Server uses word breakers to identify word boundaries in the full-text indexed data.
Type Column. Select the name of the column that holds the document type of column being full-text indexed.
NOTE: The Type Column is enabled only when the column named in the Available Columns column is of type varbinary(max) or image.
- Statistical Semantics. Select whether to enable semantic indexing for the selected column. For more information, see Semantic Search (SQL Server).
If your selected language does not have an associated Semantic Language Model, then the Statistical Semantics checkbox is not enabled. If you select Statistical Semantics prior to selecting a Language, the languages available in the drop-down combo box will be restricted to those for which there is Semantic Language Model support.
Semantic Search is not available for Azure SQL Database. The Statistical Semantics option does not appear when running this Wizard on an Azure SQL Database.
Select the change tracking options.
Select this radio button to have the full-text index updated automatically as changes occur to the underlying data.
Select this radio button if you do not want the full-text index to be updated automatically as changes occur to the underlying data. Changes to the underlying data are maintained. However, to apply the changes to the full-text index you must start or schedule this process manually.
Do not track changes
Select this radio button if you do not want the full-text index to be updated with changes to the underlying data.
Start full population when index is created (Available only when you Do not track changes).
Select this radio button to kick off a full population at the successful completion of this wizard. This will consist of creating the full-text index structure in the catalog and populating it with full-text indexed data.
Select full-text catalog
Select a catalog: Select a full-text catalog from the list. The default catalog for the database will be the selected item by default in the list. If no catalogs are available, the list will be disabled, and the Create a new catalog checkbox will be checked and disabled.
- Create a new catalog
Select full-text catalog.
Enter a name for your new full-text catalog.
b. Set as default catalog
Select to make the catalog the default for this database.
c. Accent sensitivity
Specify whether the new catalog will be accent-sensitive or accent-insensitive. If the database is accent-sensitive, Sensitive is selected by default.
d. Select index filegroup
Specify the filegroup on which to create the full-text index.
e. Select a value:
Value Description <default> If the table or view is not partitioned, select to use the same filegroup as the underlying table or view. If the table or view is partitioned, the primary filegroup is used PRIMARY Select to use the primary filegroup for the new full-text index. user-specified default filegroup If a user-defined default stoplist exists, select its name from the list to use that filegroup for the new full-text index.
Select full-text stoplist
Specify a stoplist to use for the full-text index, or disable stoplist use.
Stopwords are managed in databases using objects called stoplists. A stoplist is a list of stopwords that, when associated with a full-text index, is applied to full-text queries on that index. For more information, see Configure and Manage Stopwords and Stoplists for Full-Text Search.
Select one of the following values:
|<system>||Select to use the system stoplist on the new full-text index. This is the default|
|<off>||Select to disable stoplists for the new full-text index.|
|user-defined-stoplist-name||The list displays the name of each user-defined stoplist, if any, that has been created on the database. Select any user-defined stoplist to use for the new full-text index.|
Optionally, SQL Server only, define the population schedule. Indexing operations will begin immediately unless they have been scheduled for future execution. Schedules will be created immediately, although they will not run until their scheduled time.
New Table Schedule
Define a population schedule for a table.
New Catalog Schedule
Define a population schedule for a full-text catalog.
Edit a schedule.
Delete a schedule.
View or control the progress of the Full-Text Indexing Wizard.
Interrupts the current operation and prevents subsequent full-text operations from being performed by the wizard during this session.
When all of the operations have finished executing, click this button to access a report on the operations performed. You can view the report, print it to a file, copy it to the clipboard, or e-mail the report.