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