Friday, July 10, 2015

SQL - Full-Text Index

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:
  • 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
There are some important guidelines before creating a Full-Text index against any table which are given below:
  1. Only one full-text index is allowed per table or indexed view.
  2. 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.
  3. 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
  ON t.object_id = i.object_id 
JOIN sys.fulltext_catalogs
  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