SQL Server Performance Forum – Threads Archive

covered index

Hi, I have a clustered index on column c1 and non clustered index on c2,c3 and c4. This query Select c1 from
tablea
where c2 = … and c3=… and c4 = … can be considered as using covered index ? (as c1 will be bookmark look up)
or I have to alter the non clustered index as c2,c3,c4 and c1 to make it
covering Index ? Thanks

Similar problem, check: http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=11748
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

In that forum the columns used in where clause is refering clustered index. Here I am not using clustered index in WHERE clause Here all the columns in WHERE clause are covered in non-clustered index. when I am selecting c1, the query first searches the non-clustered index and
then has to go to bookmark lookup to get the data. As the Bookloop itself is having the required data ( i.e c1), No need for the SS
to go to the actual Row to get the data…..in other ways it works like a covering Index. But, I am not sure if it will improve performance over having nonclustered index on all
the columns. Have to check….

You were correct: If you add C1 to the tail of the non-clustered index (c2, c3, c4) it will become a Covering Index then. It will quickly find the matches for C2, C3 and C4 and will have the C1 value as well so that it doesn’t have to do a bookmark lookup.

]]>