Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 need help on Covering Indexes

Author  Topic 

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2014-10-24 : 03:47:04
i have some kind of doubts on indexes...

i would like to know what kind of queries will use the below index

Composite NonClustered index on (deptUnitID, BatchID, BitColumn ,Unitname ) INCLUDE (BatchDesc,DocID) Fields

what if one of the INCLUDE columns are noe in SELECT columnlist....?
SELECT BatchDesc -- the INCLUDE column DocID is not PRESENT in SELECT clause
FROM tableName
WHERE <deptUnitID, BatchID, BitColumn , UnitName Filters >


What if one of the INCLUDE column is part of WHERE condition ?
SELECT *
FROM TableName
WHERE <deptUnitID, BatchID, BitColumn ,Unitname and BatchDesc like '%%' > filters


Whether the Query optimizer will use created INDEX for both cases or not?

--
Chandu

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-24 : 08:50:22
Included columns generally help with predicates -- that is, JOIN ON conditions and WHERE conditions. However, in your first query, the column selection can be completely satisfied by the covering index, obviating any need to retrieve the page containing the row. Your second query is not like that, but will be helped by the included BatchDesc column included in the index, since SQL will not have to retrieve the row to check that column. OTOH "BatchDesc like '%%' " is the same as "BatchDesc <> ''" which looks cleaner to me. I'm also concerned that LIKE '%%' may not look like a SARG to the optimizer and cause a LOOKUP or Table scan. Check the query plan to be sure.
Go to Top of Page
   

- Advertisement -