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.

Straighten

Why do you need a unique constraint on the inserted column? The identity column will show what order they were inserted. I presume the database generated time will be susceptible to natural variations in the frequency with which it can process your insert requests due to concurrent activity, whether the previous insert caused a page split etc. anyway so can't really have any semantic meaning that would warrant constraining it like that. – Waggish

@martin .. Uniqueness and order aren't the same thing. I was dabbling with the idea that you can't have two rows inserted at the same time - but the precision of the getdate has proven me wrong. However I am not suggesting that what I am doing is the smartest thing in the world! – Straighten

Answer 1

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.

Sidra

I'll disagree on terminology. The resolution is 3.3mS but the precision is not! And I have seen that page which has the key sentence "All system date and time values are derived from the operating system of the computer on which the instance of SQL Server is running." which doesn't say how they are derived! – Straighten

DATETIME has a precision of 3.3ms (or thereabouts) - and is accurate to 3.3ms if you've set it from an atomic clock. :) It's GETDATE() that is the problem here, not DATETIME. Resolution is a nicer term, tho'. – Sidra

. Given that getdate returns a datetime they are sort of intertwined .. but yes, I know that GetDate is the issue here .. also check out tutelman.com/golf/measure/precision.php – Straighten

Answer 2

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

Waggish

The discrepancy isn't unreasonable as the timers don't stop while the data is inserted into the tables. I saw on this msdn.microsoft.com/en-us/library/ms188383.aspx MS page that they mentioned this when demonstrating a "select Getdate(), sysdatetime()" . However my issue seems to revolve around getdate() on my machine only returning datetime values with a granularity of around 15 mS. I will run your queries later and see what they pop out on my machine. BTW what hardware and OS are you running? – Straighten

I just took another look at your results and it looks like there must be some weird things happening during the inserts I can handle D1 != D2 on a single row, but having all those D2 values being greater than the D1 values on several different rows is really interesting (or scary). Must be some sort of strange threading issue – Straighten

@Peter - RE: Specs that was just on my desktop machine so XP, 2GB RAM probably loads of concurrent activity from other applications but still it didn't make sense how the GetDate version didn't get to 26.07 till well after sysdatetime was reporting 26.078. Each insert is in its own transaction so should have been committed before it started the next one. – Waggish