Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.

like image 344
Jonathon Kresner Avatar asked Feb 23 '11 01:02

Jonathon Kresner


People also ask

Can you use a datetime as a primary key in SQL?

A basic database-design principle is that a primary key must always be unique. And because SQL Server can't differentiate between datetime values that are within a narrow range, you must never use a datetime column as a primary key in SQL Server.

Can time be a primary key?

If you mean to create a PRIMARY KEY constraint on a column with the default value CURRENT_TIMESTAMP , then the answer is: Yes, you can.

Can date be a composite primary key?

The primary key is composite made up of the date in which the part price is being modified, and a foreign key to the Part's unique ID on the Parts Table.


1 Answers

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

like image 62
cbp Avatar answered Sep 20 '22 15:09

cbp