Overview
In this section, we will take a look at the full text index feature of SQL Server and how to create and use these indexes.
Explanation
What is a Full Text Index?
A full-text index is a special type of index that provides index access for full-text queries against character or binary column data. A full-text index breaks the column into tokens, and these tokens make up the index data. Before you can create a full-text index, you must create a FULL TEXT CATALOG, and this catalog is where the full-text index data is stored. A full-text catalog can contain many indexes, but a full-text index can only be part of one catalog. Also, only one full-text index can be created on each table, so if you have more than one column that needs to be indexed, the columns have to be moved to separate tables. It is also important to note that full-text indexes are not updated right away, as is the case with regular indexes. Populating full-text indexes can be resource-intensive, so there are more options that let you control when they are updated.
The 3 different options are:
- Full population.
- Automatic or manual population based on change tracking.
- Incremental population based on a timestamp.
More details on each of these options can be found here.
Why use a Full Text Index?
While regular clustered and non-clustered indexes give us the ability to index most column datatypes, they unfortunately are not supported for any of the large object (LOB) datatypes. Full-text indexes can, however, be created on LOB datatype columns like TEXT, VARCHAR(MAX), IMAGE, VARBINARY(MAX) (it can also index CHAR and VARCHAR column types). Without this functionality, any query that referenced a column defined with a LOB datatype would require a full scan.
How to create a Full Text Index?
Creating a full-text index does require some extra setup. As we mentioned in the opening, full-text indexes require a full-text catalog for their storage. It’s important to note that this catalog name must be unique across all databases on the server. An example of the TSQL that can be used to create a catalog is below.
CREATE FULLTEXT CATALOG fulltextCatalog AS DEFAULT; Once the catalog is created, we can create the full-text index. There are two extra options to take note of below. The first option is the “KEY INDEX”. One requirement for creating full-text indexes is that the table has a unique key defined on it. This option associates the unique key with the full-text index. In order to get the best performance, this unique key column should be an integer, usually, it’s the primary key. The second option, “STOPLIST”, associates a stop list with the index. Any tokens that are part of the stop list are not populated in the index. Here is the TSQL to create the index.
DROP FULLTEXT INDEX ON Production.Document;
CREATE FULLTEXT INDEX ON Production.Document(DocumentSummary)
KEY INDEX PK_Document_DocumentNode
WITH STOPLIST = SYSTEM;Confirm Index Usage
This simple query listed below will check the document table for any summaries that contain the word “important,” which should use the full-text index we just created.
SELECT * FROM Production.Document
WHERE CONTAINS(DocumentSummary, 'important'); Looking at the EXPLAIN plan, we can confirm it is performing a full-text search using the index and not scanning the entire table.
Ben Snaidero has been a Database Administrator for just over 10 years. Starting out working mainly with Oracle he got into SQL Server in 2005 and has worked primarily with SQL Server for the last 3 years. His main focus with both Oracle and SQL Server is in the area of performance tuning.
- MSSQLTips Awards: Achiever (75+ tips) – 2018 | Author of the Year Contender – 2016-2017
