Indexes play a crucial role in optimizing database performance by allowing faster data retrieval. SQL Server provides various types of indexes, each designed to improve query performance based on different scenarios. In this article, we will explore the key index types, their characteristics, and how they help enhance query execution.
Figure-1: B-Tree Structure
Heap Tables
Table that has no clustered index stores data in a special structure known as heap structure. Which means the data is stored as an unordered data set in each page.
- Usages - when you need faster INSERT performance for example for log or audit tables. It is faster as it stores data at the end of the last page or if is full, allocates a new page and writes the data in that page.
- Caveats - SELECT queries become slow if there is no non-clustered index. As without non-clustered index, it does full table scan which is very costly if has millions of records.
Clustered index
This is the main index type which stores the index key in a B-tree structure and actual table data in each leaf node of the index. A table can only have one clustered index defined on it as data is stored as B-tree structure.
- Usages - generally every tables have the clustered index as it makes the query faster. Using it in WHERE clauses enables database engine straight go to the table data. It also removes extra LOOK UP to retrieve additional columns. If the ORDER BY clause is on the clustered index then it removes the sort as data is already sorted.
- Caveats - INSERT/UPDATE/DELETE become slower as updating the actual key values in the index as in this case all of the associated table data also has to be moved as it is stored in the leaf node of the B-tree.
Non-Clustered index
A non-clustered index is an important index type. Similar to clustered index, the index key columns are stored in a B-tree structure, however, in leaf node a pointer to the actual table data is stored.
- Usages - similar to clustered index, it makes the query faster. Using it in WHERE clauses enables database engine straight go to the table data. If the ORDER BY clause is on the clustered index then it removes the sort as data is already sorted.
- Caveats - INSERT/UPDATE/DELETE become slower as updating the actual key values in the index as in this case all of the associated table data also has to be moved as it is stored in the leaf node of the B-tree.
Column Store Index
Column store index uses a column-based storage format to store the index data. It can be created as a clustered or as non-clustered index.
- Usages - it is designed to use for large volume of data for example data warehouse, fact tables. It offers 10 times data compression. Depending on the data, you might get 100 times query performance improvement.
- Caveats - does not support all data types like varchar(max)/nvarchar(max)/xml/text/ntext etc. You cannot use it if replication, change data capture or change tracking are enabled. DML performance is worse than row-based counter part.
Covering Index
A covering index is a non-clustered index where all columns of SELECT statements are either part of the index keys or are specified in the included column clause of the index creation statement. As necessary columns are part of the index, so no extra IO is required additional columns. Thus, it boost up the performance.
Included Columns
Included columns are used to create a covering indexes without including all the data into the key columns.
Key and Nonkey Columns
Key columns are the columns specified to create a clustered or non-clustered index. Nonkey columns are added to the INCLUDE clause of a non-clustered index. A column can be either a key column or a non-key, included column. It cannot be both. The difference between key and nonkey columns is data storage in the B-tree. Clustered and nonclustered key columns are stored at every level of the index i.e. the columns store on the leaf and all intermediate levels. However, a nonkey column is only be stored at the leaf level. Benefits of using non-key columns are:
- Columns can be accessed with an index scan.
- Data types not allowed in key columns are allowed in nonkey columns. For example, text, ntext, and image data types are allowed.
- Key columns size must be within 900 bytes enforced by SQL Server. Nonkey has no such limitation.
Filtered Indexes
A filtered index is a non-clustered index that allows us to apply specific conditions to cover a subset of rows in the table. The B-Tree will contain only those rows which satisfy the filter criteria used while creating the index. It will give performance boosting if you have a value that is used in a predicate very frequently and that value satisfies only a small amount of rows for that table.
Allowed
- Use operators like =, >=, <, IN, IS NULL, NOT NULL in the WHERE clause.
- Create multiple filtered indexes on one column. For example, if there is a status column which has the status value like 0,1,2,3. You can have an index WHERE Status = 0, and another index WHERE Status = 1.
Not Allowed
- Do not use operators like BETWEEN, NOT IN, or a CASE statement.
- Do not use date functions such as DATEADD for a rolling date range – the value in WHERE clause must be exact.
- The query optimizer won’t consider filtered indexes if you’re using local variables or parameterized SQL for the predicate that matches the filter. Jeremiah explains how dynamic SQL can help.
Full-Text Index
A full-text index is a special type of index that provides indexing support for full-text queries. It can be created on binary or character-based column types as well. In full-text index the column data is broken up into tokens and these tokens are used to build the index. These tokens are also used as a predicate to navigate the index structure. The index structure itself is also stored in its own catalog and not in the data files of the database.
Conclusion
Choosing the right type of index is essential for optimizing SQL Server performance. Clustered indexes improve sorting and retrieval, non-clustered indexes enhance search capabilities, covering indexes reduce look ups, filtered indexes optimize queries on specific data subsets and full-text index boosts up search within texts. Proper indexing strategy can significantly boost query performance and reduce resource consumption.