# How SQL-indexes with included columns work
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
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','email@example.com') insert into Persons values ('Mahrc','Zockerborg','firstname.lastname@example.org') insert into Persons values ('Ball','Gades','email@example.com')
This statement finds the customer whose email is
select email from Persons where firstname.lastname@example.org'
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
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@example.com'
Here is the execution plan:
Here first the sql server seek on the index
ix_person_mail to find the find the
(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
lastname of the datarow by the found
(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
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.
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 INDEXclause. If the index is unique, you need to add the
- 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
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.