Full-Text index
Full-Text
search supports to full-text queries against to character-based data. These
types of queries can include words and phrases as well as multiple forms of a
word or phrase. To support full-text queries, full-text indexes must be
implemented on the columns referenced in the query. The columns can be
configured with character data types (such as char and varchar) or with binary
data types (such as varbinary and image).
The
full-text index breaks the column into tokens and these tokens make up the
index data. Before creating a full-text index you must create the full-text
catalog which stored the full-text index data. 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. The full-text index is not updated like as it is 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:
1. Full population
2. Automatic or manual population-based on change
tracking
3. Incremental population based on a timestamp
Benefits of Full-Text Index
The
clustered and non-clustered indexes are not supported for any of the Large
Object (LOB) data-types. Full-text indexes can be created on LOB data-type
columns like TEXT, VARCHAR (MAX), IMAGE, VARBINARY (MAX) (it can also index
CHAR and VARCHAR column types).
How to create a full-text index?
Before
creating a full-text index we need some required setup. First, we create the
full-text catalog for their storage. Catalog name must be unique across all
databases on the server. Using the following script we can create the full-text
catalog
CREATE FULLTEXT
CATALOG ft_catalog
AS DEFAULT;
|
After
creating the full-text catalog we can the full-text index. There are two
options we can add when creating a full-text index
1-
Key Index
2-
Stoplist
For
creating full-text index a table should have a unique key and KEY INDEX
index_name is the name of the unique key index on table_name. The KEY INDEX
must be a unique, single-key, non-nullable column.
Note: Select the smallest unique key index for the
full-text unique key. For the best performance, we recommend an integer data
type for the full-text key.
Use
the following script to create full-text index.
CREATE FULLTEXT
INDEX ON Inventory.Item(ItemSummary)
KEY INDEX PK_Item_ItemsNode
WITH STOPLIST = SYSTEM;
|