I am experimenting with a program that inserts data into a SQL Server 2005 database (on XP SP3) at high speed. (This is for collecting synchronization data, so I can evaluate various aspects of my design).
My basic setup involves inserting data into a table as follows (and using SP, which simply indicates the payload field):
create table data
(
Id int PRIMARY KEY Identity,
payload datatime not null,
inserted datetime default (getdate()) not null
)
Note that both datetime fields also have UNIQUE constraints.
In the client program, I called SP in such a closed loop that I had problems with the accuracy of the .Net DateTime.Now value (and possibly the sleeping layer), and, therefore, violating the unique payload limitation. I turned to the combination of the stopwatch variable, Thread.Sleep () bit and manually created the “payload” data so that it would not violate the resolution of the SQL DateTime field (3.3 ms)
However, when inserts are generated at a speed of 5 ms to 10 ms, I began to see problems with the "Insert" field on the SQL side, where the row is regularly discarded for a unique key violation. Only when I slow down the insertion speed to more than 15 or so does this problem go away. This metric is suspiciously similar to the accuracy problem I had with .Net DateTime.Now (I read 16ms in place somewhere), so I'm wondering what the actual accuracy of the Getdate () SQL function is.
So can someone tell me what GetDate () supports, and will it be bound to the same source as the .Net DateTime.Now value? And what accuracy should I expect from him?
As an aside, I know about the DATETIME2 type on the SQL 2008 server, so the question arises as to what accuracy for GetDate () on this system also.