Beginning Indexes for SQL Server Performance

by Feb 2, 2021

Introduction

People often characterize indexes as silver bullets for the performance issues in SQL Server. Is it true? Well, yes and no. In this five-part series, we will discuss indexing for SQL Server Performance. In this first part, we will discuss beginning Indexes for Performance.

I have been doing SQL Server Performance Tuning and Optimization for over ten years, during which time I have seen both efficient use and extreme abuse of index. In this series, we will explore various real-world scenarios related to indexes and performance.

In today’s blog post, we will talk about three significant and separate types of indexes.

  • Clustered Index
  • Non-Clustered Index
  • Included Column Index

Clustered Index

A clustered index sorts and physically stores the data rows in the table (or view) based on their key values. Key values can be a single column from the table (or view) itself, or they can be multiple columns. There can be only one set of key values, so it is only possible to store the table based on a single order physically. This requirement is the primary reason a table can have only a single clustered index.

Clustered indexing is the only reason to store tables in sorted order. More specifically, in this scenario, any other method results in unordered data storage. A table without a clustered index is called a heap table, and heap tables are known for unpredictable performance. Although there are good reasons to have a heap table in data warehouse-related workloads, I prefer to have my table with a clustered index for a transactional workload.

My personal experience in general with clustered indexes has been positive. I recommend you test your workload with a table with clustered index and no index and decide which works best for your environment for that particular table.

Here is a script to create a clustered index on your table.

CREATE CLUSTERED INDEX IndexName

ON SchemaName.TableName (ColumnName); 

There is a common belief that one must use a clustered index when creating the primary key. This belief is false, and you can read here and here for additional information.

Non-clustered Index

A non-clustered index is a type of independent structure (object) from a clustered index or heap. It contains the non-clustered index key values – either one or multiple – with a pointer to the original table’s data row.

If your table has a clustered index, the non-clustered index key will implicitly include the clustered index key. If your table is a heap and you can create a non-clustered index, your index will contain a row id in your non-clustered index as a pointer to the data row containing the same key value. Suppose your clustered index is very wide – either with many columns or a very wide data type such as varchar. In this case, your non-clustered index will become wide automatically, even though it may not include many key columns. This automatic widening is one more reason you should keep a clustered index narrow with only required columns.

Here is the script to create a non-clustered index on your table.

CREATE NONCLUSTERED INDEX IndexName

ON SchemaName.TableName (ColumnName); 

The limit of non-clustered indexes per table once used to be 255 but has been increased to 999 in the recent SQL Server versions (2016 onwards).

Included Column Index

In SQL Server 2005, SQL Server has extended the non-clustered index’s functionality by adding non-key columns to the leaf level of the non-clustered index. However, to this day, I often receive questions from blog readers and clients about included columns. The non-key columns of the non-clustered index can help create cover indexes by including the data types not allowed as index key columns. Additionally, the SQL Server Server Engine does not consider the non-key columns in calculating the number of index key columns (or index key size). You can easily include non-key columns in non-clustered index to avoid exceeding the current index size limitations of a maximum of 16 key columns and a maximum index key size of 900 bytes.

Here is the script to create a non-clustered index with included key columns on your table.

CREATE NONCLUSTERED INDEX IndexName

ON SchemaName.TableName (ColumnName)

INCLUDE (ColumnList); 

If you have a SELECT statement with a WHERE condition clause, it is good to include the columns used in the WHERE clause to key columns in non-clustered key columns. In contrast, you can mention the rest of the columns from the SELECT statement within the INCLUDE column list to make the index a covering index for your query.

Summary

While indexes generally improve query performance, it is good to avoid adding any unnecessary index columns. Adding too many index columns, key columns, or non-key columns may negatively affect your performance as well. We will be discussing these topics in the future. In the next episode of this series, we will discuss a real-world scenario of over-indexing.