I have a simple SELECT * FROM MyTable WHERE DataDate = '18-AUG-2013'
query on a table that contains 340 columns and 3.4M rows.
Running estimated Execution plan in SSMS (Ctrl-L) suggests I create a non-clustered index on the DataDate and include every other column?
Is that a sensible thing to do (in general terms)?
Seems to me that this would vastly increase the indexing space and the indexing time on inserts etc. ?
Best Answer
Definitely don't do that. The missing index hints can be very useful but the recommendations can be dumb, occasionally outright ridiculous. Creating a copy of the entire table for the benefit of this query fits the later.
If your most common queries use a predicate on
DataDate
then it may be appropriate to change your tables clustered index to this. Only you can make that call based on your understanding of the workload.SELECT *
on a 340 column table smells suspicious. Do you really need all of those columns, every time?