I'm using a datetime column in SQL Server 2000 as a unique key, and I'm trying to query the data using a Microsoft Access form. I can query the data, but when I try to create two new rows that have primary keys, such as 2003-04-17 00:00:00:00 and 2003-04-17 00:00:00:01, I get an error message indicating that I've entered a duplicate key. What's the problem?
Datetime values are accurate only to within 3ms, and smalldatetime values are accurate only to within a minute. For comparison purposes, SQL Server rounds these values. For example, SQL Server treats 2003-04-17 00:00:00:00 and 2003-04-17 00:00:00:01 as identical values during an equality operation. In addition, SQL Server treats them as identical when checking whether a UNIQUE constraint or primary key is truly unique. The scripts that Listing 2 shows both produce an error that proves this rounding behavior. (For more information about SQL Server datetime data types, see the SQL Server Books Online—BOL—topic "Datetime and Smalldatetime.")
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. If you do, you'll get the following error message:
Server: Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK__
DateTimeTest__29221CFB'. Cannot insert duplicate
key in object 'DateTimeTest'.
The statement has been terminated.