CODEWITHSUNDEEP
sql-serversql-server-2008database-design
Jonathon Kresner
Jonathon Kresner

Reputation: 2843

Sql Server using DateTime as Primary Key

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.

Upvotes: 15

Views: 38054

Answers (4)

nvogel
nvogel

Reputation: 25526

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.

Upvotes: 2

Nat
Nat

Reputation: 14295

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.

Upvotes: 1

Joel Coehoorn
Joel Coehoorn

Reputation: 415690

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.

Upvotes: 5

cbp
cbp

Reputation: 25628

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

Upvotes: 6

Related Questions