Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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. ?
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?
SELECT * is actually to archive data older than a certain date - which is to be followed by a delete from - hence NOT wanting to add that index!
Aug 19, 2013 at 10:51
DELETE and the INSERT with the OUTPUT INTO clause to avoid the need for an INSERT ... SELECT followed by a DELETE.
Aug 19, 2013 at 11:14