SQL Server: Clustered index on datetime, ASC or DESC

Question

If I have an SQL Server table with a clustered index on a datetime field, that is set to DateTime.Now (from C#) before inserts, should the index be ascending or descending to avoid reorganization of the table?

Thanks.

Answer 1

Doesn't really matter - but is the DateTime really guaranteed to be unique?? I would AVOID putting a clustered index on just a DateTime - I would use a INT IDENTITY or BIGINT IDENTITY instead, and put a regular non-clustered index on DateTime (since that's really not guaranteed to be unique......)

Marc

PS: Like a primary key, the general consensus on what a clustered key should be is:

• unique (otherwise SQL Server will "uniquify" it by adding a 4-byte uniqueifier to it)
• as narrow as possible
• static (never change)
• ever increasing

The column(s) that make up the clustered key (including that 4-byte uniqueifier) are added to EVERY ENTRY in EVERY non-clustered index - so you want to keep those as slim as possible.

PS 2: the clustering key(s) are added to each non-clustered index because that's the way that SQL Server will retrieve the whole rows once it's found the search value in the non-clustered index. It's the row's "location" in the database, so to speak. Therefore, it should be unique and narrow.