Types of Microsoft SQL Server Indexes

This article describes the different types of indexes available in SQL server and how they function.

Clustered Index

A clustered index stores data rows physically in order of their key values, enabling faster search and retrieve operations.

Non-Clustered Indexes create a logical order and do not match up to physical storage on disk.

Clustered Index

Microsoft SQL server supports a type of index known as Clustered Index. This index helps organize data rows within tables by creating either by using either a primary key constraint or CREATE INDEX statement.

A clustered index can improve performance by reducing the amount of times a page from a table is read into the buffer pool. This occurs because rows in such an index are physically stored in an ordered manner according to their key value.

Indexing with this type of index can reduce the amount of pages read for a query, since rows with similar values may be stored together on one page. This makes it simpler for the database to detect prefetching sequentially, aiding query performance.

Clustered indexes can benefit many query types, but they’re particularly useful in tables with range predicates. By grouping rows with similar values together, fewer data pages need to be scanned during an index scan and the query optimizer can perform a faster search.

Whether to use a clustered index is entirely up to your application. It’s best to consider these issues before creating a table with such an index to have the appropriate index type for your needs.

Non-Clustered Index

Microsoft SQL server supports non-clustered indexes, which store pointers to data in a table rather than actual records. This index type is faster and requires less space than clustered ones containing full row structures.

Non-clustered indexes are created when a database manager needs to access information stored in non-clustered tables. They’re commonly employed for SELECT queries that retrieve data from multiple columns within an array. Other queries where scanning all rows in an index would be faster than seeking answers from the table itself.

When creating a non-clustered index, you can specify sorted data to improve query performance. Selecting this option allows the index to be automatically sorted during creation – greatly reducing both time and space needed for creation.

Non-clustered indexes can help eliminate bookmark lookup deadlocks in tables with large data sets. Furthermore, they improve query performance for queries that frequently filter or depend on predicates on strings, such as wildcard predicates used in string filters and OR operators.

Column Store Index

Microsoft SQL Server supports Column Store Indexes, which improve query performance by storing data in rows rather than by row as with traditional rowstore indexes.

Queries can selectively scan only the rows necessary to perform their desired operation, reducing the number of logical and physical reads required and improving IOPS and throughput.

These indexes also eliminate redundant values, enabling more efficient storage. This leads to a lower memory requirement, reduced disk I/O operations, more cache hits and improved page life expectancies.

Column Store Indexes are ideal for workloads requiring rapid data access, such as data warehouse query processing and operational analytics. Furthermore, they help manage memory requirements with a table optimized for memory efficiency in a memory-optimized database.

Column Store Indexes can enhance a table’s performance by maintaining its sorted order as it is compressed into the columnstore structure. This feature is especially advantageous when scanning large values or performing other queries requiring high selectivity.

These indexes can also be combined with a nonclustered B-tree index, another type supported by Microsoft SQL Server. This secondary index on a clustered columnstore index updates whenever changes occur in the primary index.

Filtered Index

Filtered indexes are non-clustered indexes that only include a subset of rows in a table or view, based on a filter condition. They’re useful for improving query performance for frequently used subsets of data and supporting queries that frequently include specific filter conditions.

Filter conditions can be specified using a WHERE clause, join clause or order by clause. Furthermore, it can take the form of either a list of values or range of values.

Filtered indexes are ideal for tables that contain many NULL values or columns frequently used in WHERE, JOIN, and ORDER BY statements. Furthermore, they’re suitable for spatial data types as they facilitate proximity searches and intersection queries.

When creating a filtered index, you can use the CREATE INDEX command to include only some of your table or view’s rows in the index. Alternatively, you may use RESTORE INDEX statement to reconstruct an existing filtered index on that same table or view, maintaining its statistics intact.

Filtered indexes can improve query performance for frequently used subsets of a table or view, but you must use them correctly. Filtered indexes require less physical space on disk, less maintenance, are simpler to scan, and provide more specific statistical metadata than their non-filtered counterparts.

Hash Index

Microsoft SQL Server supports Hash Index type of indexes. While similar to single-table join indexes in function, this index has several unique characteristics that set it apart.

Hash indexes store database objects in a hash table that can expand or contract according to demand. Depending on the application, this index may improve performance and optimize memory usage.

The cost of hash table lookups is directly proportional to the number of keys in the hash table. Therefore, the size of this database must be known ahead of time.

This type of index is ideal for queries that perform exact match searches. However, running range queries or performing operations on the hash index may harm query performance.

Additionally, hash tables have the disadvantage of storing duplicate values within each entry, particularly when the hash table size is small and key distribution is inconsistent.

Hash indexes have several distinctive qualities that make them ideal for certain applications. These features include:

Unique Index

A unique index is an essential data-store feature that optimizes query performance. It prevents duplicate entries in the column or combination of columns on which it’s defined, making search operations faster and more efficient since each row in the table has a unique index pointer.

SQL Server offers various index types, such as clustered, non-clustered, and column store indexes.

Microsoft SQL Server supports spatial indexes, which are advantageous for efficient searches on columns containing spatial data such as points, lines, or polygons. They’re often employed in distance calculations or nearest-neighbor searches.

CREATE INDEX statements create indexes. You can use this command to create single-column or multiple-column indexes, with the option of ordering in ascending or descending order.

You can also add a FILLFACTOR to an index, instructing SQL Server how to pack data in index data pages so that similar rows appear on each page when the table is indexed. However, be aware that dynamic data sets may cause your index not to populate correctly with its FILLFACTOR settings.

Finally, if you have IGNORE_DUP_KEY enabled for a table with a unique index, be sure not to enable an update that creates duplicate records. Doing so could result in data loss as SQL Server may reject these duplicates and reinsert original records – an undesirable process.

Related Microsoft SQL Server Index Articles

• Database Index Maintenance Best Practices