1

I have a database table as follows:

CREATE TABLE some_table
(   
    price                       FLOAT                   NOT NULL,
    size                        FLOAT                   NOT NULL,   
    retrieved                   DATETIME2               DEFAULT SYSUTCDATETIME(),
    runner_id                   INT                     NOT NULL,
    FOREIGN KEY (runner_id)     REFERENCES runner(id),
    PRIMARY KEY (retrieved, price, size, runner_id)
);

CREATE INDEX some_table_index ON some_table (runner_id);

This table is populated by sets of price/size data retrieved from a web service which is essentially time-series in nature. As far as I can tell (and I have put some comparison logic in my code to make sure) price and size are never the duplicated in a single set of entries retrieved from the web service. They may however be duplicated in subsequent requests for price/size data related to the same runner.

I am getting intermittent primary key constraint duplicate key exceptions even though I am forming my key off a high resolution date time value as well as the rest of the table columns. At this stage I am considering dropping the composite key in favor of an auto-generated primary key. Can anyone suggest why this might be happening based on the table schema? I consider it unlikely that I am trying to insert two separate sets of price/size data with duplicate values simultaneously given the nature of the code and resolution of the date time value. I guess it is possible though - I am using asynchronous methods to interact with the database and web service.

Thanks

NinjaDeveloper
  • 1,620
  • 3
  • 19
  • 51
nefuller
  • 13
  • 3
  • Are you providing a value for your timestamp or are you using the default value generated by `SYSUTCDATETIME()` – derpirscher Jul 21 '16 at 16:23
  • Hi, I tried relying on the default value generated by 'SYSUTCDATETIME()' as well as providing a value via 'DateTime.UtcNow()' - had the same problem in both cases. I have a solution based on what @bds mentioned regarding precision in his comment below. – nefuller Jul 23 '16 at 07:13

1 Answers1

1

Is each runner_id inserting multiple rows into the table in bulk? It's possible the same price and size would be processed in less than 100 nanoseconds. This would result in them not being unique.

SQL Server obtains the date and time values by using the GetSystemTimeAsFileTime() Windows API. The accuracy depends on the computer hardware and version of Windows on which the instance of SQL Server is running. The precision of this API is fixed at 100 nanoseconds. The accuracy can be determined by using the GetSystemTimeAdjustment() Windows API.

https://msdn.microsoft.com/en-us/library/bb630387.aspx

bds
  • 156
  • 1
  • 8
  • Thanks! This appears to have been the case. I found a solution for generating unique time stamps here: [link](http://stackoverflow.com/questions/1416139/how-to-get-timestamp-of-tick-precision-in-net-c). I used the solution by @Ian Mercer and I'm no longer getting the exceptions. – nefuller Jul 23 '16 at 07:18