Many developers must work with SQL Databases. Most of the time they must creat an index. But there are some differences between the index types. One of them is the Index with included column.

For my example we will use a Persons-Table.

create table Persons
(
    [Id] [int] IDENTITY(1, 1) NOT NULL,
    firstname varchar(255),
    lastname varchar (255) null,
    email VARCHAR (255) not null,
    CONSTRAINT PK_ID PRIMARY KEY (Id)
)

Lets fill the Table with some example data

insert into Persons values ('Harrisn','Ford','ha@online.com')
insert into Persons values ('Mahrc','Zockerborg','mz@online.com')
insert into Persons values ('Ball','Gades','bg@online.com')

This statement finds the customer whose email is mz@online.com:

select
    email
from 
    Persons
where 
    email='mz@online.com'

When you look at the execution plan you will notice that the sql engine will search through each row and scan the complete table for the data.

Now lets create an unique index onto the email column.

CREATE UNIQUE INDEX ix_person_email 
ON dbo.persons(email); 

Now when you execute the command above again,you will notice, that the sql processor will now search in the index for the data and returning then the matches.

However, consider the folliwng example:

select
    email,
    firstname,
    lastname
from 
    Persons
where 
    email='mz@online.com'

Here is the execution plan:

Here first the sql server seek on the index ix_person_mail to find the find the email and the id. (You will notice it in the Output List section).

Second, the sql server then uses the key lookup on the index of the Persons-Table to find the firstname and lastname of the datarow by the found id. (See Output List section)

Third, for each row found in the index, it matches with rows found in the PK-Index. usinf nested loops.

As you can see the cost for the key lookup is about 50% of the query, which is quite expensive.

To help reducing thie keylookup cost, the SQL Server allows you to extend the functionality of a index by including non-key columns (aka. included columns).

By including non-key columns in non-clustered indexes, you can create nonclustered indexes that cover more queries.

Note that when an index contains all the columns referenced by a query, the index is typically referred to as covering the query.

#Create index with included column

First, drop the index ix_person_email from the Persons table:

DROP INDEX ix_person_email 
ON dbo.Persons;

Then create the new index with the included columns:

CREATE UNIQUE INDEX ix_person_email_inc
ON dbo.persons(email)
INCLUDE(firstname,lastname);

Now, the query optimizer will solely use the non-clustered index to return the requested data of the query

An index with included columns can greatly improve query performance because all columns in the query are included in the index; The query optimizer can locate all columns values within the index without accessing table or clustered index resulting in fewer disk I/O operations.

#Syntax

The following illustrates the syntax for creating an index with included columns:

CREATE [UNIQUE] INDEX index_name
ON table_name(key_column_list)
INCLUDE(included_column_list);

In this syntax:

• First, specify the name of the index after CREATE INDEX clause. If the index is unique, you need to add the UNIQUE keyword.
• Second, specify the name of the table and a list of key column list for the index after the ON clause.
• Third, list a comma-separated list of included columns in the INCLUDE clause.

#Conclusion

SQL Server can be complex, but take some time to understand the index usage, by determining the execution plans. You may use more indexes with included columns in future. Because you will ever use a non indexed column to retrieve data.