Jeff Moden wrote:
Understood. I knew what your perspective was. I was a bit concerned that you might not know what others are doing with their queries. I also don't know what the table is actually being used for and so can't anticipate the needs. My suggestions are just based on other needs. And, to be sure, if the only thing that fits your perspective is the only thing where the column will be a search criteria, I wouldn't waste the Clustered Index on the query. A nice, single column NonClustered Index reduces the number of read to just 3 pages, even on the 300 Million row monster. Not having all the other pages caused by a read-ahead into memory will also save memory for other things that can use it better.
If, however, other also hit the column with search criteria and they do it a lot along with returns of related columns, then setting up a Clustered Index on it might be the way to go. If you're going to do that, might as well spend the extra 4 bytes to make it UNIQUE, as well.
Be advised that the process of building such a Clustered Index on that 300 Million row table will cause the old heap (or whatever) to remain until the new index is created and then the old heap will be dropped. If you don't have any freespace in the MDF, that will cause, according to my calculations, your MDF file to grow by ~54.5GB.
Unfortunately we don't have efficient ways of knowing how every table is used, but we can't design them for every circumstance anyway. But I'm sure the date doesn't need to be and can't be the key for this one, and I do need a key based on other fields so I may as well make it clustered. I'm not sure I need it, but I went ahead and created a non-clustered index on the date for testing, which didn't take too long, but used 5 times the space of the table as I forgot the table is compressed! Thanks for the heads up on the space, we do have plenty right now but we are trying to reduce our usage further so I just don't want permanent objects that aren't necessary taking up space.
I will weigh the pros and cons of adding a unique identifier.