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

When you say the dates will be unique, do you mean you think they will be 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.