Be careful using a datetime column as part of a primary key
For no appartent reason an app that had worked fine for two years all of a sudden started failing with primary key violoations. Of course I had “optimized“ it shortly before it started failing, but I didn't see how my changes could have caused this problem. I only removed a few lines of code and didn't change anything related to data access. My change was intended to make the code run faster, but nothing else.
Well, my changes sped it up and that's exactly what was wrong. Because it was running faster, data was being written to the database more often. One of the fields being written was “LastUpdate“ which was defined as datetime data type and was part of the primary key.
The datetime data type in SQL Server is only accurate to one three-hundredth of a second, so although the app may have been passing two different values to SQL server for consecutive records, the values were being rounded which was causing the primary key violation.
For example, assume I pass these values to a stored procedure:
2004-05-13 13:12:19.393
2004-05-13 13:12:19.394
2004-05-13 13:12:19.395
2004-05-13 13:12:19.396
2004-05-13 13:12:19.397
SQL Server rounds them to this:
2004-05-13 13:12:19.393
2004-05-13 13:12:19.393
2004-05-13 13:12:19.397
2004-05-13 13:12:19.397
2004-05-13 13:12:19.397
It's easy to see how this was causing my problem. SQL Server always rounds to .000, .003, or .007 milliseconds.
I found no reason to have the LastUpdate field as part of the primary key, so I removed it and all worked well. Another lesson learned!