That said, I would consider adding a surrogate key (e.g. an identity column) and use the date and surrogate key as your clustered index keys. (Date first, surrogate key second).
Very interresting point there... can you elaborate a little more?
I will have a sell job to do to DBAs
They seem to think that a surrogate key for that table will just add overhead and increase table size without any added value
What is the gain over using all the IDs ?
Then, based on your analysis of the queries against that table, I'd add some nonclustered indexes to support the most common and/or expensive queries.
Most IDs in that table contains very few values
There are 600 DateIds, 40 OtherId1, 350 OtherID2 and the OtherId3, for what I know, is rarely used in queries
My guess is that the nonclustered indexes would add little to no benefit over hashing
But I also take good note on your "Mesure and Manage" advice
Thanks a lot !!
Vince Poirier