15

Hi my questions is similar to:

MySQL: Using DATETIME as primary key

But I'm specifically interested in Sql Server and I want to approach the question practically with a specific scenario in mind rather than theoretically as in the other post.

I want to store events/actions that users perform. The odds of more than one user performing an action in the same 100ms gap is very low and infrequent collision are acceptable. If I could discretely express 10ms or even 1ms gaps then I'm very happy with the risks.

Thus begs the question, can I use a DateTime as my primary key instead of a unique identifier, because I will be regularly querying the latest 100 events and sorting the events by the time which they occurred.

Community
  • 1
  • 1
Jonathon Kresner
  • 2,793
  • 5
  • 29
  • 40
  • 8
    If you're always sorting by the event time and rarely/never sorting by the identity, you should leave the identifier on there but create the clustered index on the EventTime column instead of the identifier. – Phill Feb 23 '11 at 01:57

4 Answers4

6

Yes you can, but it sounds like a very bad idea to me. If you are really worried about performance, you can use a sequential unique identifier, an auto-incrementing integer, or you can give the DateTime column its own clustered index (recommended).

cbp
  • 25,252
  • 29
  • 125
  • 205
5

If you really want to avoid a surrogate key for this (ID/identity column), I would at least combine the datetime column with the user id column for a composite key. This sounds like a more natural fit for your data.

Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
2

In SQL Server 2008, use DATETIME2, not DATETIME. You can achieve upto 100 nanosecond precision in SQL Server 2008.

If you sometimes need to record more than one row for a given time, however infrequently, then I'm not sure what you are trying to achieve by making the date and time into a key. Important criteria for choosing keys are Familiarity, Simplicity and Stability. On that basis, assuming it does make sense for your requirements, a date and time seems like a sensible choice.

nvogel
  • 24,981
  • 1
  • 44
  • 82
1

In order for infrequent collisions to be acceptable you really cannot use the timestamp as there will be collisions.

You could code around collisions by using a smart procedure that modifies the datatime slightly, but why bother when an index on the timestamp would suffice and be far easier to implement.

This article is informative on precision issues specific to C# however.

Nat
  • 14,175
  • 5
  • 41
  • 64