Geek Answers Handbook

SQL Server primary key in datetime field

I am 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 set a unique restriction on it.

Which table structure is better and why?

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

or

MyDate (PK, datetime)
MyValue (int)

I feel that I do not want the artificial PK (MyIndex) in this table, because it is not needed and because the dates will be unique, I will use them to access any record. However, it may be that it is more efficient to have an artificial PC ...?

+5
source share
4 answers

When you say that the dates will be unique, do you mean that you think they will be unique, or is their uniqueness guaranteed by the statement of the problem? In my experience, some things turn out to be much less unique than imagination (social security numbers are an example for the United States).

If date values ​​are not guaranteed to be unique, you must add an integer key.

If date values ​​are guaranteed unique, do they change? If they change, do they refer to other tables? If both answers are yes, you should probably add an integer key.

, . DATETIME - 8 , INTEGER - 4 , . - , , , SMALLDATETIME 4 .

+9

DATETIME IE :

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

... , mydate . IE:

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

... , @my_date_value - , . , - , , .

+2

, . , (, , ) , , hunky dory.

+1

, ( ), datetime .

If you are only going to insert increasing datetimes, then creating a clustered index in the primary key column is also a good choice.

+1
source

More articles:


All Articles