covered index | SQL Server Performance Forums

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
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:
Luis Martin
Moderator 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… 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.