index seek with lookup vs index seek with include

[OceanDeep via SQLMonster.com]

I have a table tbltest with 10 fields and say field A is in a clustered index
and field B is in a nonclustered index ix-B. I have a query as follows:

Select C, D, E, F, G, H , I, J from tbltest where B ='something'

With the existing indexes, execution plan will do a index seek using ix-B and
then do a lookup to get the resul. Is it better to change the index ix-B to

CREATE NONCLUSTERED INDEX [IX_b] ON [dbo].[tbtest]
(B
include C, D, E, F,G, H, I, J))

the above syntax may not be the exact but I hope you get the idea. In
general, will this index with 'include' be better than lookup if all the
columns are needed in the result? Let assume the table itself will have some
inserted activity but not often? Edit activity will be very minial.

od

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-performance/201005/1

[Erland Sommarskog]

OceanDeep via SQLMonster.com (u46587@uwe) writes:
> I have a table tbltest with 10 fields and say field A is in a clustered
> index and field B is in a nonclustered index ix-B. I have a query as
> follows:
>
> Select C, D, E, F, G, H , I, J from tbltest where B ='something'
>
> With the existing indexes, execution plan will do a index seek using
> ix-B and then do a lookup to get the resul. Is it better to change the
> index ix-B to
>
>
> CREATE NONCLUSTERED INDEX [IX_b] ON [dbo].[tbtest]
> (B
> include C, D, E, F,G, H, I, J))
>
> the above syntax may not be the exact but I hope you get the idea. In
> general, will this index with 'include' be better than lookup if all the
> columns are needed in the result? Let assume the table itself will have
> some inserted activity but not often? Edit activity will be very
> minial.

As always when it comes to performance questions, the answer is: it depends.

If you look only at this particular query: yes, it will perform faster if
you include all the columns in the SELECT list. But:

1) The index will take up more space.
2) Maintenance of the index will be more expensive.
3) If someone later adds one more column to the SELECT list, but is
oblivious about the index, the index is now only a burden.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx