INCLUDE option in CREATE INDEX

What is the difference in the following CREATE INDEX statements when using
the INCLUDE option?
CREATE INDEX index1 ON table1(col3) INCLUDE(col1, col2)
CREATE INDEX index1 ON table1(col3, col1, col2)
I have read BOL on the INCLUDE option and it says that it "Specifies the
nonkey columns to be added to the leaf level of the nonclustered index."
Isn't that also being done when creating a composite index without the
INCLUDE option?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200606/1Consider this:
drop table a
go
create table a(i int, j int)
create unique index a1 on a(i) include(j)
insert into a values(1,2)
go
insert into a values(1,3)
(1 row(s) affected)
Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'dbo.a' with unique index
'a1'.
the index guarantees uniqueness of i but also stores j on its leaf
level.
Why would I need that? For index covering. I can have one and the same
index guartantee uniqueness of i and cover a query select i,j from a
where i between 1 and 10.|||Hi cbrichards
1) Included columns are in the leaf level ONLY. The do not appear in the
higher levels, as the key column of an index do.
2) The included columns play no part in the ordering of the leaf level
rows.
You may have a composite key of lastname, firstname and an included
column of city
The leaf row for Smith, Jane in Orlando may come before or after the
leaf row for Smith, Jane in Seattle. There is no predicting it or
controlling it.
3) With Included columns, you can exceed the key size limit of 900 bytes
total, and 16 columns.
This is the biggest benefit. You can get a lot more covering index
situations if you can get around the 900 byte limit.
--
HTH
Kalen Delaney, SQL Server MVP
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:622b4dffd8727@.uwe...
> What is the difference in the following CREATE INDEX statements when using
> the INCLUDE option?
> CREATE INDEX index1 ON table1(col3) INCLUDE(col1, col2)
> CREATE INDEX index1 ON table1(col3, col1, col2)
> I have read BOL on the INCLUDE option and it says that it "Specifies the
> nonkey columns to be added to the leaf level of the nonclustered index."
> Isn't that also being done when creating a composite index without the
> INCLUDE option?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200606/1|||In addition to the other replies: because the included columns are not
part of the index branch pages, each branch page can carry more index
keys. This results in a smaller index and potentially in a better index
depth. The lower index depth saves logical reads for every index seek.
Also, it should be a little bit less expensive to process changes in
included column when compared to indexed columns, because only the leaf
page(s) will have to be updated.
So if you need the column, but will never be filtering its value, then
it is smart idea to include it instead of indexing it.
HTH,
Gert-Jan
"cbrichards via SQLMonster.com" wrote:
> What is the difference in the following CREATE INDEX statements when using
> the INCLUDE option?
> CREATE INDEX index1 ON table1(col3) INCLUDE(col1, col2)
> CREATE INDEX index1 ON table1(col3, col1, col2)
> I have read BOL on the INCLUDE option and it says that it "Specifies the
> nonkey columns to be added to the leaf level of the nonclustered index."
> Isn't that also being done when creating a composite index without the
> INCLUDE option?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200606/1|||Gert-Jan Strik wrote:
> So if you need the column, but will never be filtering its value, then
> it is smart idea to include it instead of indexing it.
More to the point: even if you filter on it, but the first column(s) is
selective enough, including instead of indexing might be more
efficient.
Also consider a unique index on employee_id including email_address.
The query
select employee_id, email_address from ... where email_address like
'%stone'
will use the index all right. Makes sense?|||Correct. The point of the included columns is, that no bookmark lookup
in the base table is required to access the column's value.
Side note: often, situations such as employee_id for an email_address
are already covered, because often employee_id is be the clustered index
key (default index type for the primary key), and thus automatically
part of any nonclustered index on email_address.
Gert-Jan
Alexander Kuznetsov wrote:
> Gert-Jan Strik wrote:
> >
> > So if you need the column, but will never be filtering its value, then
> > it is smart idea to include it instead of indexing it.
> More to the point: even if you filter on it, but the first column(s) is
> selective enough, including instead of indexing might be more
> efficient.
> Also consider a unique index on employee_id including email_address.
> The query
> select employee_id, email_address from ... where email_address like
> '%stone'
> will use the index all right. Makes sense?|||I put that in a blog with better explanations:
http://sql-server-tips.blogspot.com/2006/06/yet-another-index-covering-tip.html