Understanding Database Indexes – (Part – 3)

Author: Basit A. Farooq

Full-text indexes

A full-text search is a word search based on character string data. The Microsoft Full-Text Engine for SQL Server creates and maintains a full-text catalog automatically when you enable a table for full-text search.

For more information about full-text indexes, see SQL Server Books Online topic: “Populate Full-Text Indexes”.

XML indexes

An XML index is a persisted representation of the data contained in an XML data type column. XML indexes have different procedures for creation and management than standard indexes, and are structured differently than standard indexes. There are two primary XML index types: primary and secondary. You must create a primary index first and can then create one or more secondary indexes. When creating XML indexes, the base table must have a primary key constraint defined. If the base table is a partitioned table, XML indexes use the same partitioning function and partitioning scheme. Moreover, you can create one primary index and one or more secondary indexes for each XML column in the base table. If you use data type methods, you should create at least a primary index. All data type methods use the primary index for optimization, if present.

For more information about XML indexes, see SQL Server Books Online topic: “XML Indexes (SQL Server)”.

Memory-optimized indexes

You create memory-optimized indexes on memory-optimized tables. You can only create nonclustered indexes on memory-optimized tables. The nonclustered indexes of memory-optimized tables are structured as Bw-tree. The Bw-tree is a high performance latch free B-tree index structure that exploits log-structured storage. The following figure illustrates the Bw-tree architecture:

Like memory-optimized tables, the memory-optimized indexes also reside in memory. You can create two types of nonclustered indexes. These are:

• The nonclustered memory-optimized hash indexes – These indexes are made for point lookups. They do not have pages and are always fixed in size. The values returned from a query using a hash index are not sorted. Hash indexes are optimized for index seeks on equality predicates and also support full index scans. Queries using hash indexes returns results in an unsorted order.
• The nonclustered memory-optimized non-hash indexes –These are made for range scans and ordered scans. They support everything that hash indexes supports plus seek operations on inequality predicates such as greater than or less than, as well as sort order. Queries using non-hash indexes returns results in a sorted order.

For more information about memory-optimized indexes, see extensive set of documentation at the Microsoft “Introduction to Indexes on Memory-Optimized Tables” web page.

Columnstore Indexes

Columnstore indexes were first introduced with SQL Server 2012, to significantly improve performance of data warehouse workloads. Columnstore indexes allow you to deliver predictable performance for large data volumes. According to Microsoft, for certain data warehousing analytical queries, you can achieve up to 10x performance improvements by using in-memory columnstore indexes. SQL Server 2012 implementation of in-memory columnstore indexes are not updatable, which means that you cannot perform DML operations on tables once the in-memory columnstore index is created on them. So, to update data in the underlying table, you must first drop or disable the columnstore index, and then enable or recreate the columnstore index once the data in the underlying table is updated. SQL Server 2014 removes this restriction and introduced updatable in-memory columnstore indexes.

Unlike SQL Server 2012 Database Engine, which only supports nonclustered columnstore indexes, SQL Server 2014 Database Engine supports both clustered and nonclustered columnstore indexes. Both these types of SQL Server 2014 columnstore indexes use same in-memory technology but have the different purpose. SQL Server 2014 clustered columnstore indexes are updatable, meaning you can perform DML operations on the underlying table without having to disable or remove the clustered columnstore index.