In my Are you ready for SQL Server 2012 or are you still partying like it is 1999? post, I wrote about how you should start using SQL Server 2005 and SQL Server 2008 functionality now in order to prepare for SQL Server 2012. I still see tons of code that is written in the pre 2005 style and people still keep using those functions, procs and statements even though SQL Server 2005 and 2008 have much better functionality.

Today we are going to take a look at Indexes With Included Columns

When you use Indexes With Included Columns you can do two things that you cannot do with regular non clustered indexes

1) You can go over the 900 byte limit

2) You can use data types not allowed as index key columns

By including nonkey columns, you can create nonclustered indexes that cover more queries. To learn more about covering indexes, take a look at SQL Server covering indexes written by George Mastros

Let’s get started to see what I mean, first create these two identical tables

SELECT * INTO Test1 FROM sys.objects

SELECT * INTO Test2 FROM sys.objects

Now create this index on the Test1 table

CREATE INDEX ix_Test1 ON Test1(type,is_ms_shipped)

Running a simple query reveals a seek

SELECT type,is_ms_shipped 
FROM Test1
WHERE type = 's'
AND is_ms_shipped = 1

Now let’s add the name column to the columns returned or to the WHERE clause

SELECT type,is_ms_shipped,name 
FROM Test1
WHERE type = 's'
AND is_ms_shipped = 1


SELECT type,is_ms_shipped 
FROM Test1
WHERE type = 's'
AND is_ms_shipped = 1
AND name LIKE 's%'

As you can see, we get a scan now, this is because the index is not covering the query

Create the following index on the Test2 table, as you can see the only difference bwteween the index on the Test1 table is that it has the name as an included column

CREATE INDEX ix_Test2 ON Test2(type,is_ms_shipped) INCLUDE (name)

Running the first query where the name is in the WHERE clause against the Test2 and the Test1 table shows much better performance for the index with the included column, this is of course expected

SELECT type,is_ms_shipped 
FROM Test2
WHERE type = 's'
AND is_ms_shipped = 1
AND name LIKE 's%'

SELECT type,is_ms_shipped 
FROM Test1
WHERE type = 's'
AND is_ms_shipped = 1
AND name LIKE 's%'

As you can see, we get an index seek now for the table with the index that has included columns

Running the second query where the name is in the WHERE clause as well as returned in the results against the Test2 and the Test1 table shows much better performance for the index with the included column, this is of course expected

SELECT type,is_ms_shipped,name 
FROM Test2
WHERE type = 's'
AND is_ms_shipped = 1
AND name LIKE 's%'



SELECT type,is_ms_shipped,name 
FROM Test1
WHERE type = 's'
AND is_ms_shipped = 1
AND name LIKE 's%'

And we get a seek as well for the table with the index that has included columns