> ## Content Index
> Fetch the complete content index at: https://blog.bajonczak.com/llms.txt
> Use this file to discover other available public pages before exploring further.

# How SQL-indexes with included columns work
- URL: https://blog.bajonczak.com/sql_index_sql_index_include/
- Published: 2021-09-27T22:00:00.000Z
- Updated: 2023-11-30T08:04:29.000Z
- Author: Sascha Bajonczak
- Tags: SQL Server

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.

```sql
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

```sql
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`:

```sql
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.

```sql
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:

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

```

Here is the execution plan:

![](https://notes.bajonczak.com/assets/img/index_seek_nested_loops.e191042d.png)

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.

# [**#**](https://notes.bajonczak.com/sql%5Findex%5Fsql%5Findex%5Finclude/?ref=blog.bajonczak.com#create-index-with-included-column)Create index with included column

First, drop the index `ix_person_email` from the `Persons` table:

```sql
DROP INDEX ix_person_email 
ON dbo.Persons;

```

Then create the new index with the included columns:

```sql

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.

# [**#**](https://notes.bajonczak.com/sql%5Findex%5Fsql%5Findex%5Finclude/?ref=blog.bajonczak.com#syntax)Syntax

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

```sql
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.

# [**#**](https://notes.bajonczak.com/sql%5Findex%5Fsql%5Findex%5Finclude/?ref=blog.bajonczak.com#conclusion)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.