Full-Text indexes are a special type of
token-based functional indexes. They are built and maintained by the Microsoft
Full-Text Engine for SQL Server. They provide efficient support for
sophisticated word searches in character string data.
Due to special type of token-based
functionality, the building process of a full-text index is fairly different
from other types of indexes. MSFTESQL builds an inverted, stacked, compressed
index structure based on individual tokens from the text being indexed.
A logical full text index consists of one or more internal tables
which are known as fragments. When the data from the source table is updated
then additional fragments are created automatically. Internally, full text
queries need to "read" all of the fragments. As a result, too many
fragments can lead to degraded performance.
Note: Only one full-text index is allowed per table or indexed view,
and each full-text index applies to a single table or indexed view. A
full-text index can contain up to 1024 columns.
|
Full-text search refers to the
functionality in SQL Server that supports full-text queries against
character-based data. Full-text indexes must be implemented on the columns
referenced in the query to support full-text queries because this kind of
queries can include multiple forms of a word or phrase.
Permissions
Due to special type of token-based functionality, a user must have REFERENCES permission on the full-text catalog and have ALTER permission on the table or indexed view, or be a member of the sysadmin fixed server role, or db_owner, or db_ddladmin fixed database roles.
The process of creating and maintaining a full-text index is called index population. Microsoft supports the following types of full-text index population:
Due to special type of token-based functionality, a user must have REFERENCES permission on the full-text catalog and have ALTER permission on the table or indexed view, or be a member of the sysadmin fixed server role, or db_owner, or db_ddladmin fixed database roles.
The process of creating and maintaining a full-text index is called index population. Microsoft supports the following types of full-text index population:
- Full population
- Change tracking-based population
- Incremental time stamp-based population
SQL Server supports the following types of population: full population, change tracking-based automatic or manual population, and incremental time stamp-based population.
Full
Population
During a full population, index entries
are built for all the rows of a table or indexed view. A full population of a
full-text index; builds index entries for all the rows of the base table or
indexed view. By default, SQL Server populates a new full-text index fully as
soon as it is created.
Important guidelines before creating a Full-Text Index
To understand the Full-Text Index, we are creating Item Master table having SKU, SKU_Desc and Comments columns as given below:
Important guidelines before creating a Full-Text Index
There are some important guidelines
before creating a Full-Text index against any table which are given below:
- Only one full-text index is allowed per table or indexed view.
- A full-text search key must be a unique, non-nullable, single-column index which is not offline, is not defined on a non-deterministic or imprecise nonpersisted computed column, does not have a filter, and has maximum size of 900 bytes.
- For creating full-text index, user must have FullText Catalog.
To understand the Full-Text Index, we are creating Item Master table having SKU, SKU_Desc and Comments columns as given below:
SKU
|
SKU_Desc
|
Comments
|
1
|
RML SPECIAL EYESW EYELNR BLK MAGIC
|
RML SPECIAL EYESW EY
|
2
|
KELLOGGS CRML RICE KRISPIES SQRS x6 132G
|
KELLOGGS CRML RICE K
|
3
|
Olay regenerist daily serum 50ml
|
Olay regenerist dail
|
4
|
FRUCTIS STYLE SURF WAX 75ML
|
FRUCTIS STYLE SURF W
|
5
|
Alnwick India Pale Ale 500ml
|
Alnwick India Pale A
|
6
|
Gillette Deo Bodyspray Cool Wave 150ml
|
Gillette Deo Bodyspr
|
7
|
RML Glam EYESW Pro Liq Liner BLK Glamo
|
RML Glam EYESW Pro L
|
8
|
Courage best ale 500ml
|
Courage best ale 500
|
9
|
Diet Coke 24x330ml
|
Diet Coke 24x330ml
|
10
|
IMP LEATHER SKIN KIND BATH HYDRATE 500ML
|
IMP LEATHER SKIN KIN
|
Unique
Index and FullText Catalog
Now, we have to create unique index
on the Item Master table and FullText Catalog also as given below:
----- create unique index on the table
CREATE UNIQUE INDEX ui_ukSku
ON dbo.tbl_item_master(SKU);
----- create catalog for full-text index
CREATE FULLTEXT CATALOG cat_Items
WITH ACCENT_SENSITIVITY =
OFF
|
Create FullText Index
Finally, the example creates a
full-text index on the SKU_Desc column, using the cat_Item catalog, ui_ukSku
unique index and the system stoplist as given below:
----- create FullText index on the table
CREATE FULLTEXT INDEX ON dbo.tbl_item_master(sku_desc)
KEY INDEX ui_ukSku on cat_Items
WITH STOPLIST = SYSTEM;
|
How
to view FullText Index
To view FullText Index, we need to
use the following SQL Script:
SELECT t.name
AS TableName,
c.name AS
FTCatalogName
FROM sys.tables t
JOIN sys.fulltext_indexes
i
ON t.object_id = i.object_id
JOIN sys.fulltext_catalogs
c
ON i.fulltext_catalog_id = c.fulltext_catalog_id
|
Conclusion
The information in full-text indexes
is used by the Full-Text Engine to compile full-text queries that can quickly
search a table for particular words or combinations of words. A full-text index
stores information about significant words and their location within one or
more columns of a database table. A full-text index is a special type of
token-based functional index that is built and maintained by the Full-Text
Engine for SQL Server.
Reference: https://msdn.microsoft.com/en-us/library/ms187317.aspx
Type of Indexes in SQL
No comments:
Post a Comment