4

I am contemplating using a datetime2(7) as a clustered index.

If I were to always populate that column using SYSDATETIME is it possible for the same instance to issue the same value twice (assuming we don't do something like set the system clock back)?

Alternatively, what is a cohesive strategy for ensuring uniqueness on such a column, even if it that comes at a cost of time precision?

For my use, I really only care about the seconds, but I will be doing a lot of range queries on this column so the CI seems appropriate.

Improve this question
1

2 Answers 2

Reset to default
2

Yes. The resolution of the Windows clock is decent, but not perfect. You should use another field, such as an INT IDENTITY, to ensure uniqueness. But go ahead and cluster on the date first, if it suits your needs.

Alternatively, you could use a DATETIME PK, but supervise inserts with a stored procedure that verifies the uniqueness of the incoming date, and pauses if necessary. I can whip up an example if you'd like.

Edit:

Another option, borrowing from ypercube's comment, would be a non-unique clustered index on the date field, and a unique non-clustered primary key on the ID field. This would keep your other indices smaller, as they would point to the narrow ID column, but would also entail two hops to get to actual data.

Improve this answer
8
  • 1
    You can also cluster on the datetime only (and nothing else) without having it unique. Since the accuracy is milliseconds, if it rarely has duplicates, the CI will be not much wider than a DATETIME index.
    – ypercubeᵀᴹ
    Commented Oct 17, 2013 at 20:23
  • I could easily cluster on a smaller datetime2 I was concerned about the possibility of duplicates in the CI. Of course I know I can CI on an INT ... but that data will never be range-searched in my use case... which is the entire point of the question :D
    – Matthew
    Commented Oct 17, 2013 at 21:34
  • @ypercube: You could, and the system would create a hidden "uniquifier." Personally, I'd rather have it handled in the foreground, so to speak, but the width would be the same (four bytes), so YMMV.
    – Jon of All Trades
    Commented Oct 17, 2013 at 21:50
  • 1
    I thought the uniquifier was appended only for the rows that have duplicate value in the rest of the CI. I could be wrong.
    – ypercubeᵀᴹ
    Commented Oct 17, 2013 at 21:56
  • I'm no expert, I've never had occasion to use one, but I would assume that the field has to exist regardless, so the system has a place to put a value if a duplicate ever appears. Sounds like a good separate question!
    – Jon of All Trades
    Commented Oct 17, 2013 at 22:02
0

Be careful choosing your clustered index KEY because it will be propagated to all non clustered indexes of your table. Datetime2 values are 8 bytes length, you may consider an INT IDENTITY like Jon said.

Improve this answer
1
  • 1
    I actually recommend using both, in a compound key. Clustering is very efficient for range filters, which are common for dates; the ID field would serve to disambiguate in the case the OP is asking about: where two records are created with the same date/time.
    – Jon of All Trades
    Commented Oct 17, 2013 at 21:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.