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.
| 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) Fieldswhat if one of the INCLUDE columns are noe in SELECT columnlist....?SELECT BatchDesc -- the INCLUDE column DocID is not PRESENT in SELECT clauseFROM tableName WHERE <deptUnitID, BatchID, BitColumn , UnitName Filters >What if one of the INCLUDE column is part of WHERE condition ?SELECT * FROM TableNameWHERE <deptUnitID, BatchID, BitColumn ,Unitname and BatchDesc like '%%' > filtersWhether 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. |
 |
|
|
|
|
|
|
|
need help on Covering Indexes