Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Covering Index using Included Columns

By Josef Richberg,

Introduction

Non-clustered indexes are very common and often times misunderstood. In this article I will explain how to use them effectively by using included columns.

Definition

The image below is a stylized picture of a non-clustered index on a heap table. It shows each page of the index (doubly linked list) and how each entry points to the next level. One record in the root points to a complete page in the following intermediate level. One record in the Intermediate level points to a page in the next level. In the image below the next level happens to be the leaf level,but in a very large index you may have multiple intermediate levels between the root and leaf. At the very end the leaf points to the actual table pages

 

 

When you create an non-clusterd index in SQL Server 2005/2008 you have the option of columns for the key and what are called 'included' columns. Here is what it looks like if you create an index with all of the necessary columns (SARG and columns in the Select statement) as keys.

 

 

Each entry requires 46 bytes at each level. If you do some quick math you will see that for an index that has 4 levels (Root to Leaf) you will need 46*4=184 bytes * number of entries (depends upon number of records in your base table). Just concern yourself with the 184 bytes, since we will see shortly how you can reduce that..

You might be asking yourself at this point "184 bytes isn't that much", but another benefit of reducing the size of the entry is this will reduce the number of index pages, which will make searches more efficient. We will see how to do that shortly. For the image below, we have taken Keys 1-3 as actually SARGS and keys 4-5 as only necessary to produce a covered index.

Real World Example

Here is an example to show you what to look for to make sure your index is properly 'covered'. The query I have is selecting 3 columns back and using 1 of the 3 as a SARG, search criteria.

Select Statment

I created a simple non-clustered, non-unique index on C017_DateLastRecieved (here is a partial image of the create statement):

Create index statement

The result of both the execution plan and the output from statistics io is listed below. Notice the RID lookup.

execution plan 1

staistics io 1

Now let's add two additional columns, the ones in the select statment as included columns in the index. You will notice that you cannot adjust the order when you select them. Order for included columns, unlike index key columns, has no meaning. The included columns, are again, simply to provide information to the select statement.

create index with included

Now we get to see the result of adding those two columns in the included columns section:

showplan 2

The showplan above pulls all of the information from the index alone, which is exactly what we want. To back that up look at the statistics io. In the original example above we had 143 logical reads, whereas now we have:

stats io 2

4 logical reads! We reduced our logical reads from 143 to 4. That is a dramatic improvement. Logical reads are a very good way to determine if you are improving your queries., but that is for another article :)

Conclusion

If you look above, you will see that when you use 'included columns', the entry is ONLY included at the leaf level. This saves you quite a bit of space. The new space requirement is (16*3)+46=94 bytes/entry. This saves you 1-(94/184)=48.9% in space requirements, which will reduce the space needed by the same amount. You might be asking 'when do you I use an included column vs. a key'? The answer is quite simple. Anything that is in the where clause would be a key, anything in your select statement would be an included column.

Total article views: 12006 | Views in the last 30 days: 11
 
Related Articles
ARTICLE

Stairway to SQL Server Indexes: Level 5, Included Columns

Included columns enable nonclustered indexes to become covering indexes for a variety of queries, im...

FORUM

index entry

index entry

FORUM

Non Cllustered Index - Composite vs Include Columns

How different is a composite index from a normal index with include option?

FORUM

Included Columns

Why not include every table field in the included columns list for every index?

FORUM

Question about included columns in nonclustered indexes: Where exactly are they stored?

Do nonclustered indexes just store pointers to clustered indexes?If so where are the included column...

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones