The objective of this SQL Server tutorial is to teach you how to include non-key columns in an index to improve query performance.
What is SQL Server Index with Included Columns?
The ability to include additional non-key columns in an index to augment its capacity was introduced in SQL Server from SQL Server 2005. The inclusion of non-key columns benefits query processing by eliminating the need to consult multiple indexes or the main table. It does so by making all the relevant column data available in the index. Normally SQL server gets some of the required column values from the index and for the remaining column values it has to do a key lookup on the main table or the default clustered index which is costly both in terms of resources and time. An index including the relevant non-key columns eliminates that extra work.
An Index with Included Columns can only be created on a nonclustered index. The non-key columns which are included are those which are most frequently queried along with the key column.
SQL Server Index with Included Columns Syntax
The basic syntax of the command to create a non-clustered index including non-key columns is as below.
CREATE NONCLUSTERED INDEX index_name
In this syntax,
- NONCLUSTERED – this NONCLUSTERED keyword is optional. Even if nothing is specified SQL Server will interpret it as request to create a NONCLUSTERED index and create one.
- column_list – comma separated list of key columns. It might be a single column or more than one.
- INCLUDE – SQL keyword to include additional columns in an index.
- included_column_list – comma separated list of additional or non-key columns. It might be a single column or more than one.
SQL Server Index with Included Columns Simple
Let us consider a table called customer which contains basic customer information. The table is represented below. The custid column in the table is the primary key and has a default clustered index associated with it. The email column contains customer email ids which is frequently queried and on which we will base our example.
Suppose we generate the estimated query execution plan for the below query to fetch a row of information for a particular customer with the email id ‘firstname.lastname@example.org’.
SELECT fname, lname, email
The query execution plan will be as below. We can see from it that it does an index scan of the default primary index which is the only index available.
Query Execution Plan
We know that a scan is costlier and slower as it has to go through all the records in the index or table. So to improve the situation we will create a non-clustered index including the fname and lname columns in the index.
Creating the Nonclustered Index with Included Columns
The below command creates a nonclustered index called ix_email_additional with the fname and lname columns included as non-key columns on the customer table.
CREATE NONCLUSTERED INDEX ix_email_additional
INCLUDE (fname, lname);
The same can also be created from the SSMS (SQL Server Management Studio) GUI in the following steps.
- Right click in the Index node under the customer table in Object Explorer and select New Index – > Non-Clustered Index.
2) In the General tab of the New Index dialog box that will open, type in the name of the index in the Index Name textbox. Then go to the Index Key Columns tab on the middle of the dialog and add the key column email using the Add button on the right as shown in the figure below.
3) Once added it will show in the window as below.
4) Then go to the Included Columns tab and add the fname and lname column by clicking on the Add button in the exact same manner as you did before.
5) Once added it will show up in the main dialog. You can then complete the process by clicking on the OK button to create the new non-clustered index with the selected non-key columns and it will be created.
You can check the newly index using below T-SQL command or from the SSMS GUI as follows.
EXEC SP_HELPINDEX customer;
|ix_email_additional||nonclustered located on PRIMARY|
|PK_customer||clustered, unique, primary key located on PRIMARY||custid|
Checking the New Index in Action
Now let us generate the estimated query execution plan for the above query with the new index in place. As we can see from the screenshot this time the query execution plan is different and it is using an index seek on the newly created non-clustered index with included columns. So we can see the difference it makes.
Query Execution Plan
Getting things in perspective
However, if we change the query as below and generate the query execution plan for the same, we will see a different plan.
SELECT * FROM customer WHERE email@example.com';
Query Execution Plan
In the above plan we can see that SQL Server will first do a seek on the non-clustered index with the included columns and then it will do a key lookup (i.e. lookup required columns values corresponding to the key from another index or the main table where the data exists) on the default clustered index. This is because the non-clustered index does not contain the values for the other columns (i.e. city, contact) required by the query as the query is a SELECT * query to fetch the column values of all columns for customer with email id ‘firstname.lastname@example.org’. The query result is displayed below.