Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Related: stackoverflow.com/a/10104128/565869. –  Jon of All Trades Oct 17 '13 at 16:58
add comment

2 Answers 2

up vote 2 down vote accepted

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 sproc 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.

share|improve this answer
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 Oct 17 '13 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 Oct 17 '13 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 Oct 17 '13 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 Oct 17 '13 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 Oct 17 '13 at 22:02
show 3 more comments

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.

share|improve this answer
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 Oct 17 '13 at 21:48
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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