RaviinBne (1/4/2011)
and this goes on...
If you want help checking for duplicate (redundant) indexes, you'll need to post the whole lot, not a sampling
and due to this I am having to include all columns in the include column list of all indexes to avoid lookups...
Well that's why your index is so large. By doing that you're duplicating the entire table with each index. Including every column is almost never a good idea. Run some traces, see what queries are being run, index accordingly.
Covering indexes are all well and good, but the general though is that you can't cover all queries and you shouldn't try to cover all queries.
Questions
1) would it be better for me to just have the ID as the included column so that SQL server uses the covering index and clustered index to retrieve the data.
If the ID is the cluster, it's in the index anyway, regardless of whether or not you add it.
If your index is covering, SQL won't go to the cluster for additional columns. It's only when the index is not covering that a lookup is necessary.
Maybe the lookups are OK, maybe not. You'll have to test to see.
2) Does SQL server use the included columns in a convering index to Search?
No. Only key columns can be used in seeks and queries can only seek on an index if they're filtering on a left-based subset of the index key.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability