XML FeedsIn 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
Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
Thanks Denis, this article proved useful for a colleague at work :D
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.