THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Roman Rehak

Why use "Included" columns in SQL Server 2005

Recently I was working on an application where I got a lot of mileage out of using a new feature of SQL Server 2005 called included columns. When you add a column as an “included” column, it gets stored at the leaf level of the index and it is not part of the index key. This only works for non-clustered indexes.

The main advantages of included columns are 

1) You can exceed the 900 byte limitation of the index key, any columns that would push you over that limit could instead be added as included rather than key columns

2) You can include datatypes that are not allowed as key columns, such as varchar(max), nvarchar(max) or XML. Note – you still cannot use the old datatypes such as text or ntext.

3) You can greatly expand you options for creating covering index.

4) You can reduce the size of the index to make it more efficient by including only lookup columns in the key, and then adding other query covering columns as included columns
 
As always, you should use some judgment. If you include four varchar(max) columns and they contain a lot of data, the size of your index will be huge. Also, since each column will be maintained in the table and in the index, updating large columns will slow down the application somewhat. Below is a sample CREATE INDEX script that indexes the Title column and includes a varchar(max) column called Content:

CREATE NONCLUSTERED INDEX [IX_MyTable] ON [dbo].[MyTable]
(

      [Title] ASC

)
INCLUDE ( [Content])

Published Thursday, May 03, 2007 12:15 PM by roman

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Alex Kuznetsov said:

Roman,

Let me add one more advantage:

Suppose you have a table:

create table a(email_alias varchar(20), full_name varchar(20), /*many other columns*/)

Suppose you need to make sure email_alias are unique. Obviously you will create a unique index on email_alias. Suppose you also need an index on (email_alias, full_name) to cover several frequently run queries. In SQL Server 2005 you can have one and the same index accomplish both goals. Use a new INCLUDE option in CREATE INDEX statement:

create unique index a1 on a(email_alias) include(full_name)

Both columns will be stored in the index, which will guarantee index covering, but the uniqueness of email_alias will be also preserved:

--- succeeds

insert into a values('jsmith', 'Jack Smith')

go

--- fails

insert into a values('jsmith', 'Jared Smith')

Msg 2601, Level 14, State 1, Line 1

Cannot insert duplicate key row in object 'dbo.a' with unique index 'a1'.

The statement has been terminated.

It is very important to keep the number of indexes as low as possible, and the new INCLUDE option comes very handy in accomplishing that goal.

Does it make sense to you?

May 3, 2007 4:11 PM
 

roman said:

Yes, it does make sense and you make a good point. With this new feature the same index can serve as a unique index with the index key columns and extend it's functionality as a coverig index by adding included columns.

May 4, 2007 2:03 PM
 

beltway said:

I did not understant what you meant by

"4) You can reduce the size of the index to make it more efficient by including only lookup columns in the key, and then adding other query covering columns as included columns ..."

The main thing i did not understand is this part of the sentence

" and then adding other query covering columns as included columns ..."

what exactly you mean by that.

can you please explain by emailing me to beltway@gmail.com

Thanks in advance.

December 19, 2007 4:48 PM
 

Jack said:

I appreciate you are discussing a 2005 feature , what is the recommended way to index a column of VARCHAR(2500) in sql server 2000

January 9, 2008 12:20 PM
 

hihihi said:

good

September 8, 2008 5:34 AM
 

hihihi said:

good

September 8, 2008 5:34 AM
 

hoho said:

what if i only wants to use only one column for index, but it's a varchar(2000). Let's say I want to create a unique url...

October 6, 2008 11:19 PM
 

sushil said:

will we have the uniqueness applicable to our included columns also?

March 24, 2009 8:57 AM
 

Raja said:

It a revolutionary feature for Existed Non-Clustered Index , which was implemented in Sqlserver 2005. It provides comfortable options for choosing and covering higher number of non-cluster indexes in a table. Can enhance performance of queries if disk space is high.

July 6, 2009 9:58 AM
 

savio said:

Do we need a covering index for a delete?

September 6, 2011 6:52 PM
 

maxlin dodti said:

if we have existing index of key values and wants to add included clolumns then will the query using before  will use the new index(changed index)??

November 8, 2013 2:03 AM

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement