Defining Full-Text Indexes

Before you can run a full-text query, a full-text index has to exist on the table you want to search. In modern SQL Server, defining that index is the only setup step. There is no separate database-level "enable" switch to flip first, because every user database has been full-text capable by default since SQL Server 2008. Two prerequisites still have to be in place before you begin:

• The Full-Text Search component must be installed during SQL Server setup.
• The target table must contain character-based columns, such as varchar or nvarchar, and must have a unique, non-nullable, single-column index, usually the primary key.

With those in place, there are two ways to define a full-text index: through the SSMS wizard, or directly in Transact-SQL.

SQL Server Management Studio provides a GUI-based Full-Text Indexing Wizard that walks through the same steps a CREATE FULLTEXT INDEX statement would otherwise require, which makes it a reasonable way to get started if you are new to full-text search.

1. Launch SSMS and connect to your SQL Server instance.
2. In Object Explorer, expand Databases, then your database, then Tables.
3. Right-click the target table, point to Full-Text index, and choose Define Full-Text Index... to launch the wizard.
4. Step through the wizard:
   • Select Unique Index: choose a unique, non-nullable index, often the primary key.
   • Select Columns: check the text columns to include, and optionally choose a language per column, such as English, 1033.
   • Select Catalog: choose an existing full-text catalog, or create a new one, for example Emp_Notes.
   • Change Tracking: choose Automatic (the default), Manual, or Do not track changes.
   • Populate Index: optionally choose "Start full population" to build the index immediately.
5. Review the summary and click Finish. The wizard runs the equivalent CREATE FULLTEXT INDEX statement in the background.

Verifying the index:

• In SSMS, expand Tables, then your table, then Full-Text Indexes, and you should see the newly created index.
• In T-SQL:

SELECT *
FROM sys.fulltext_indexes
WHERE object_id = OBJECT_ID('dbo.YourTable');

What the wizard does internally, expressed as the equivalent T-SQL:

CREATE FULLTEXT CATALOG Emp_Notes;

CREATE FULLTEXT INDEX ON Employees
(
    Notes LANGUAGE 1033
)
KEY INDEX PK_Employees
ON Emp_Notes
WITH CHANGE_TRACKING AUTO;

Once population finishes, you can test the new index:

SELECT *
FROM Employees
WHERE CONTAINS(Notes, 'performance OR leadership');

It is worth being precise about what the wizard is actually doing behind the scenes, since some of this is easy to describe in misleading terms:

• Enabling full-text search for the database: there is nothing to do here. Full-text search has been enabled by default for every user database since SQL Server 2008; there is no per-database "enable" step, implicit or explicit, for the wizard to perform. DATABASEPROPERTYEX('YourDatabaseName', 'IsFullTextEnabled') still exists and can be queried, but on a modern instance it always returns 1; it is kept only for backward compatibility with older scripts that check it.
• Selecting which tables participate: this is real. Right-clicking a specific table and launching the wizard registers that table against the full-text catalog you choose or create, and a single catalog can hold indexes from multiple tables.
• Selecting which columns participate: this is also real. In the "Select Columns" step you choose exactly which columns to index; only character-based and document-content columns are selectable, and a language ID can be assigned per column.

The Change Tracking step is worth a second look, since the three options behave quite differently once an index exists:

• Automatic keeps the index current on its own, in the background, as the underlying rows change. This is the default and the right choice for most tables.
• Manual still records which rows have changed, but nothing is pushed into the index until you explicitly run a repopulation, typically ALTER FULLTEXT INDEX ... START UPDATE POPULATION. This gives you control over exactly when the index-refresh cost is paid, at the price of search results that can lag behind the data until you trigger an update.
• Do not track changes (CHANGE_TRACKING OFF in T-SQL) records nothing at all. The index drifts out of date as soon as the underlying data changes, until you explicitly run a full or incremental repopulation. This option exists for cases with heavy write activity and infrequent searches, where the overhead of tracking every change is not worth paying.

Either approach ends at the same place, a full-text index defined on the table, but they suit different situations:

• The wizard is well suited to exploring the options interactively, or to a one-off index you are defining by hand while you are still learning how the pieces fit together, since every choice is presented to you with no syntax to remember.
• CREATE FULLTEXT INDEX is the better choice for anything that needs to be repeatable: a deployment script, a database project, or any setup that has to run identically across development, test, and production environments. It can also be reviewed and version-controlled the same way as any other schema change.

Once an index exists, it is managed the same way regardless of how it was created. ALTER FULLTEXT INDEX adds or drops columns and changes properties such as change tracking, and DROP FULLTEXT INDEX removes the index entirely:

ALTER FULLTEXT INDEX ON Employees
ADD (Department LANGUAGE 1033);

DROP FULLTEXT INDEX ON Employees;

A full-text index on an existing table was historically defined through system stored procedures rather than through dedicated DDL. That changed with SQL Server 2005: CREATE FULLTEXT INDEX is the standard, dedicated Transact-SQL statement for defining a full-text index, fully integrated with both the engine and the SSMS UI, and it is the only approach worth using in new development:

CREATE FULLTEXT INDEX ON Employees
(
    Notes LANGUAGE 1033
)
KEY INDEX PK_Employees
ON Emp_Catalog
WITH CHANGE_TRACKING AUTO;

Before CREATE FULLTEXT INDEX existed, in SQL Server 2000 and earlier, full-text catalogs, tables, and columns were configured through a family of system stored procedures. These procedures still exist in current SQL Server for backward compatibility, but Microsoft's own documentation marks every one of them for removal in a future version, so none of them belong in anything new you write.

An example of the legacy style, shown only to illustrate what an old script might contain, not as something to write today:

EXEC sp_fulltext_catalog 'Emp_Catalog', 'create';
EXEC sp_fulltext_table 'Employees', 'create', 'Emp_Catalog', 'PK_Employees';
EXEC sp_fulltext_column 'Employees', 'Notes', 'add';

Putting it together: the Employees table lives in the DT database and has a Notes column holding English text, plus an existing unique, non-nullable index, PK_Employees. An integer key is preferred for the key index. Neither the catalog nor the index exists yet.

USE [DT];
GO

CREATE FULLTEXT CATALOG [Emp_Notes];
GO

CREATE FULLTEXT INDEX ON [dbo].[Employees]
(
    [Notes] LANGUAGE 1033
)
KEY INDEX [PK_Employees]
ON [Emp_Notes]
WITH CHANGE_TRACKING AUTO;
GO

No database-level enable call, no separate column-add step, and no activation call are needed. Creating the catalog and the index is the entire setup. Initial full population starts automatically, and later changes propagate in the background under CHANGE_TRACKING AUTO. Give population time to finish before testing:

SELECT [Notes]
FROM [dbo].[Employees]
WHERE CONTAINS([Notes], N'project');

In the next lesson, you will write queries to access full-text catalogs.