How to keep datetime2 column inserts unique

Question

I have a table that stores stock ticks in sql server 2008. It is keyed on the symbol column and the datecolumn which is a datetime2. In a c# app inserts are sent in at a high rate to the table. Currently a row is made using the symbol and DateTime.Now and then inserted. Occationaly the situation can arise where 2 ticks for the same symbol come in and the datetime.now will return the same time, down to the millisecond. My question is how can I assure that my inserts do not use the same time for a given symbol. I fiqure I could have another unique key like rowver or somekind of autonumber in the db as one solution, but even if the insert is successful I dont want there to be duplicte times for a given symbol. I've also thought about storing the last insert time and just adding a millesecond to it if it is equal. I'm not sure the best way to go about this. Also the datetime2 seams to have more precission than the .net datetime, so don't know if I should try to solve this on the server or the client.

Thanks

I want to keep every insert no matter what and for performance reasons I don't want to go with checking prior values, so the solution I will go with is an identity column. That will allow me to preserve the order of the inserts while allowing duplicates in the datetime2.

Answer 1

I think the answer to this will depend on what you want to do in the event a symbol does come in at exactly the same time?

You could create a unique index on the symbol and the datetime column - but this would cause a unique key violation for one of the two instances. If you're designing for performance, this could be an expensive exercise.

Another option is to have a timestamp column, which would insure that even if you do happen to get two identical symbols at the exact same time, your insert will not fail.

The real question is - what do you want to do with that second identical symbol? Can you afford the expense of checking to see if an identical symbol and datetime already exists in the table before you write to it? So you could then log and discard the duplicate?

Answer 2

Are your database inserts thread safe? I.e. is it possible two inserts could happen at the same time and be unaware of each other? In this case, storing the highest and adding a millisecond wouldn't work.

You might have to have a regular procedure that goes through and increments a time in the case of a collission.

Answer 3

Don't use the datetime in the key.

Use an identity column as the primary key, and just put an index (could be clustered if you want) on the tick+datetime. I'm not sure what you want with the dups, but this way you will be able to record that they happened...

Answer 4

Don't use C# DateTime.Now, even though DateTime.Now is thread-safe it can return two identical values. You would be better off using the autogenerated property of your Datetime2 column.

edit:... by setting the autogenerated property to SYSDATETIME()