SQL Server primary key on datetime field

Question

I'm creating a new table in SQL Server 2005 that needs 2 fields: DateTime and MyValue (Int32). The DateTime field will be unique so I will be setting a unique constraint on it.

Which table structure is better and why?

MyIndex (PK, int)
MyDate (datetime) (IX_UniqueKey)
MyValue (int)

or

MyDate (PK, datetime)
MyValue (int)

My feeling is that I don't want an artificial PK (MyIndex) in this table because it is unnecessary and because the dates will be unique I will use them to access any record. However, it may be that it's more performant to have an artificial PK...?

Answer 1

Nope, your intuition is correct. As long as no one can slip two (or I suppose more) simultaneous events in on you given the available resolution of your data collection process, you are hunky dory.

Answer 2

If you have the guarantee that the datetimes will always be unique (think resolution of the time component), then creating the Primary key on the datetime column is a good choice.

If you are only ever going to be inserting increasing datetimes, then creating the clustered index on your primary key column is also a good choice.

Answer 3

If the DATETIME value will be populated by the database IE:

INSERT INTO your_table
  (mydate, myvalue)
VALUES
  (GETDATE(), 1234)

...then yes, making the mydate column the primary key is the ideal solution. If the date is provided by the application IE:

INSERT INTO your_table
  (mydate, myvalue)
VALUES
  (@my_date_value, 1234)

...assuming @my_date_value is not being supplied by the database - no, not ideal. An datetime from anything other than the database can't be guaranteed to be accurate based on insertion.

Answer 4

When you say the dates will be unique, do you mean you think they will unique, or their uniqueness is guaranteed by the statement of the problem? In my experience, some things turn out to be a good deal less unique than one imagines (US social security numbers being an example).

If the date values are not guaranteed unique, you should add the integer key.

If the date values are guaranteed unique, do they change? If they do change, are they referenced by other tables? If both answers are "yes" you probably should add the integer key.

If the date values are guaranteed unique, and don't change or are not referenced, you can use them for the key. Regular DATETIMEs are 8 bytes and standard INTEGER values are 4 bytes which might have a minor effect on indexing. If your date values are just dates, or only exact to the minute or less, and in the more restricted range allowed by the type, you can use SMALLDATETIME and get those index values down to 4 bytes.