Index Design: clustered index using a DATE

Question

Every once in a while, one needs a check. This is such a time.

I have tables that are used for reporting. They are deleted/inserted once a day or multiple times a day to "refresh the data" from the source always using a continuous date range (like month-to-date or a rolling 45 days). The source data has no ID or uniqueness to them.

The convention so far has been to use a clustered index on the date - every table has a date and every query uses a date (99% of the time). If the table has a column(s) that makes it unique, I have added those to the clustered to make it unique (e.g., status and email). If not, I have added an ID IDENTITY to make it unique.

Research seems to advise "use an incrementing ID" with few deviations. However, performance using this convention has been excellent - unless I am missing something somewhere.

So while this has been good, sometimes a peer check can be a good thing :)

• Any issues with date as the leading column in a clustered index (considering the above)?
• Is adding MY OWN ID Identity column for uniqueness better than MS SQL doing it for me?
• Would adding ID first, then date be better?

Environment: Azure SQL

Answer 1

Any issues with date as the leading column in a clustered index (considering the above)?

None that I can think of off the top of my head. It's typically good to cluster on a column that is a non-random / sequential type. Dates typically fit this criteria well, as opposed to GUIDs (UNIQUEIDENTIFIERS) which are all over the place. The clustered index is the logical sorting of the actual table (into a B-Tree data structure). By using a non-random or sequential data type as the index key, this allows maximum efficiency when seeking to that subset of the B-Tree for all of the relevant rows.

Is adding MY OWN ID Identity column for uniqueness better than MS SQL doing it for me?

I know of no reason to do this, but I'm going to assume you're not harming much by doing it because you're using an IDENTITY column which I'm also assuming rarely changes. The only drawback is you're making your clustered index a little fatter. If you plan to use any nonclustered indexes, they'll become slightly heavier as a result too, since all nonclustered indexes store the clustered index key as well. If you planned to use a column that was updated frequently as part of your clustered index key then that would be not so good for performance since every time the key value changed, it would have to update all the related rows in all of the nonclustered indexes as well, causing a lot of additional overhead with blocking.

Would adding ID first, then date be better?

No, probably not. If you queries aren't using the ID field in the predicates (JOIN, WHERE, HAVING clauses), then leading with it in your index makes that index no longer applicable to those queries. Again, this is because the data is logically sorted in the order of the fields defined in the index. If the B-Tree was sorted by ID first, but your query didn't filter by ID, then the SQL Engine would have no efficient way to traverse the B-Tree directly to the subset of rows that your query does filter on.