Precision of SQL Getdate?

Question

I am experimenting with a program that inserts data into an SQL 2005 Server database (on XP SP3) at high rate of speed. (This is for collecting timing data so I can evaluate different aspects of my design).

My basic set up involves inserting a data into a table like the following (and using an SP that just specifies 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 have UNIQUE constraints on them as well.

On the client program I was calling the SP in such a tight loop that I had problems with the precision of the .Net DateTime.Now value (and possibly thread sleeping as well) and hence violating the payload's unique constraint. I addressed by a combination of a stopwatch variable, a bit of Thread.Sleep() and manually constructing the "payload" data so that it didn't violate the resolution of the SQL DateTime field (3.3 mS)

However with the inserts being generated at a rate between 5mS and 10mS I have started to see issues with the "Inserted" field on the SQL side where a row is being rejected regularly for a unique key violation. It is only when I slow my insert rate to more than 15 or so mS that this problems goes away. This rate is suspiciously like the precision issue I had with the .Net DateTime.Now (I read 16mS on a post somewhere) so I am wondering what the actual precision of the SQL Getdate() function is.

So can someone tell me what is backing GetDate(), and would it be tied to the same source as the .Net DateTime.Now value? And what sort of precision should I expect from it?

As an aside I know about the DATETIME2 type in SQL 2008 server, so that raises the question as to what the precision is for GetDate() in that system as well.

Answer 1

DATETIME is stored as 2 integers. One representing the date part and the other the time part (number of ticks after midnight) each tick is 1/300 of a second so it has at least a theoretical precision of 3.3 milliseconds.

I just tried running this on my machine

declare @d  varchar(24)

while 1=1 
begin
set @d=CONVERT(VARCHAR(24), GETDATE(), 113)
raiserror('%s',0,1, @d) with nowait
end

And got a fairly lengthy run where it did go up one tick at a time so I don't think there can be any inherent limitation that prevents it achieving that.

01 Aug 2010 00:56:53:913
...
01 Aug 2010 00:56:53:913
01 Aug 2010 00:56:53:917
...
01 Aug 2010 00:56:53:917
01 Aug 2010 00:56:53:920
...
01 Aug 2010 00:56:53:920
01 Aug 2010 00:56:53:923

Regarding your query about GetDate() precision in SQL Server 2008 this is the same as for SQL2005. sysdatetime is meant to have higher precision. I just tried running the following and was surprised by the discrepancy between the two results.

SET NOCOUNT ON

CREATE TABLE #DT2(
[D1] [datetime2](7) DEFAULT (getdate()),    
[D2] [datetime2](7) DEFAULT (sysdatetime())
) 
GO

INSERT INTO #DT2
          DEFAULT  VALUES
GO 100

SELECT DISTINCT [D1],[D2],DATEDIFF(MICROSECOND, [D1], [D2]) AS MS
 FROM #DT2

Results

D1                           D2                              MS
----------------------------    -----------------------      ------
2010-08-01 18:45:26.0570000   2010-08-01 18:45:26.0625000     5500
2010-08-01 18:45:26.0600000   2010-08-01 18:45:26.0625000     2500
2010-08-01 18:45:26.0630000   2010-08-01 18:45:26.0625000     -500
2010-08-01 18:45:26.0630000   2010-08-01 18:45:26.0781250     15125
2010-08-01 18:45:26.0670000   2010-08-01 18:45:26.0781250     11125
2010-08-01 18:45:26.0700000   2010-08-01 18:45:26.0781250     8125

Answer 2

DATETIME has a precision of 3.3ms, but GETDATE() does not return times accurate to this as you've discovered. Check out the MSDN page for date/time types/functions for more info on how the new types / functions work.