knechod (2/17/2009)
Hugo Kornelis (2/17/2009)
Anyhow, never indexing a datetime is not a wise decision.
More of an avoidance than a hard-and-fast rule. As an ordering method, not a problem. As a clustered index, ok. I'm a little concerned about how a clustered datetime would affect multiple users inserting at the same time.
No problem at all. A clustered index doesn't have to be unique. Admitted, there are drawbacks to having a nonunique clustered index, but if the benefits exceed those drawbacks, then by all means go for it. 😉
Your mentioning inserting also reminds me that I forgot to include an important benefit of clustering on a datetime - if said datetime is the moment of inserting, you'll avoid time-consuming page splits. (The same advantage applies to identity and to GUID with a default of NEWSEQUENTIALID(), but not to GUID with a default of NEWID()).
But try querying for 2/16/09 8:17pm (the time right now MST), in a table where the datetime is acting like a timestamp. It's more like indexing a real number.
If I need to find rows with that EXACT time (to the millisecond), I would filter for DateInserted = '2009-02-16T20:17:00.000'. If I need to find all rows inserted in that minute, I'm back at the old interval problem already discussed at length in this thread, except the interval is now a minute instead of a day:
WHERE DateInserted >= '2009-02-16T20:17:00.000'
AND DateInserted < '2009-02-16T20:18:00.000'
Both the exact match and the range will benefit from a clustered index. The exact match will also benefit from a nonclustered index; the range match might benefit.
As for high selectivity, I DO index GUIDs. But I would never make a user enter one!
Finally, the datetime datatype is not inexact.
Yes, dates are integers at some level. But my users' concept of dates don't extend to 3.333 milliseconds.
The reason why inexact values are generaly less useful in an index is not tied to the users' concept of the values, but to conversion and representation issues. Even though conversion from float to text and back is (probably) documented somewhere, it's too complicated to predict. And that can cause weird results:
SET @a = 134.7;
SET @b-2 = 10.0;
SET @b-2 = 13.47;
IF @a = @b-2 PRINT 'Equal' ELSE PRINT 'Inequal';
For datetime, I could make a similar repro, using a millisecond value that doesn't end in a 3, 7, or 0. But that is very predictable, and hence easily avoidable. (The floating point issue is not predictable at all - I had to try various combiniations of values in the code above before I got the desired results...)
Actually, the above is mainly a discussion on why you should not equality search on inexact values, such as floating point numbers. You can safely use range search (e.g., match all values between 13.46999 and 13.47001). And just as with datetime, an index can still be valuable even in range search operations.
Bottom line: Inexact numbers make a bad primary key column - this goes 100% for floating point numbers, and, hmmm, let's say 95% for datetime values. But depending on use, inexact numbers can be a prime candidate for a clustered index, and can also be considered for nonclustered indexes.