Introduction

Let's pretend you are being interviewed for the DBA or T-SQL Programmer job and are asked a question like this:

What is the precision of the GETDATE() and SYSDATETIME() functions?

If your answer is:

"Well, GETDATE() function returns DATETIME data type so the precision is 3.33 ms, the SYSDATETIME() function returns DATETIME2 data type so the precision is 100 ns."

Read on to find out if you are correct.

Testing the precision of GETDATE()...

In order to find out what the correct answer should be we can run some simple SQL queries. To test the GETDATE() function lets run this query

DECLARE @TimeStart DATETIME
DECLARE @Time DATETIME
DECLARE @TimeEnd DATETIME
DECLARE @I INT
DECLARE @Count INT
SET @I = 0
SET @Count = 0
SET @TimeStart = GETDATE()
SET @Time = @TimeStart
WHILE @I < 10000000
BEGIN
 SET @TimeEnd = GETDATE()
 IF @TimeEnd <> @Time
 BEGIN
 SET @Count = @Count + 1
 SET @Time = @TimeEnd
 END
 SET @I = @I + 1
END
PRINT @Count
PRINT DATEDIFF(millisecond, @TimeStart, @TimeEnd) / CAST(@Count AS REAL)

On my system I got the following values: 595 and 15.6521

So what does all this mean? Let's look closer at what the code does. We have a loop that calls the GETDATE() function 10 million times. The value returned by GETDATE() is assigned to a variable. If that value is different then the last value returned by GETDATE(), the counter gets incremented. After we are done looping we have two numbers.

The first number tells us how many times during this process the GETDATE() function returned a new value. In our case it is 595 which means that out of 10 million calls we got only 595 distinct values returned by GETDATE(). The second number tells us how frequently this value gets changed or in other words, what is the precision of the GETDATE() function. In our case it turns out to be 15.6521 milliseconds.

Why not 3.33 ms? The 3.33 ms is the maximum precision of the DATETIME data type, unfortunately it is not the precision of the GETDATE() function. The GETDATE() function is based on a system timer that is refreshed 64 times a second or every 15.625 milliseconds.

Please note that above applies to SQL Server 2000, 2005 and 2008. The next section is SQL Server 2008 specific.

Testing precision of SYSDATETIME()

To test the SYSDATETIME() we have to modify the query slightly:

DECLARE @TimeStart DATETIME2
DECLARE @Time DATETIME2
DECLARE @TimeEnd DATETIME2
DECLARE @I INT
DECLARE @Count INT
SET @I = 0
SET @Count = 0
SET @TimeStart = SYSDATETIME()
SET @Time = @TimeStart
WHILE @I < 10000000
BEGIN
 SET @TimeEnd = SYSDATETIME()
 IF @TimeEnd <> @Time
 BEGIN
 SET @Count = @Count + 1
 SET @Time = @TimeEnd
 END
 SET @I = @I + 1
END
PRINT @Count
PRINT DATEDIFF(microsecond, @TimeStart, @TimeEnd) / CAST(@Count AS REAL)

The logic of the query is the same but some sharp eyes would notice few differences. The DATETIME data types were replaced by DATETIME2 (SYSDATETIME returns DATETIME2 data type and we would not want to truncate the values, would we?). We also would want to change the "millisecond" to "microsecond" in the DATEDIFF call to get more accurate results.

After running the query on the same system I got the following values: 15910 and 979.615. As you can see the SYSDATETIME() is more precise then GETDATE(). In the same loop of 10 million calls the SYSDATETIME() generated 15910 distinct values vs 595 generated by GETDATE(). This translates to precision of about one millisecond - which is way short of the precision of the 100 nanoseconds the DATETIME2 data type provides.

It is obvious the SYSTDATETIME() uses a more precise timer that is refreshed about every millisecond.

Additional Issues

I tested the queries on several systems using different versions of Windows and different versions of SQL Server, but I have to admit I was not able to test every possible combination of OS/SQL Server pairs. The behavior of GETDATE() calls was consistent across OS and SQL Server versions. The SYSDATETIME() was a bit more tricky.

I have access to SQL Server 2008 on Windows XP and Windows Server 2008 only. The 1 millisecond precision was available only on Windows Server 2008. On Windows XP the precision of SYSDATETIME() and GETDATE() are exactly the same. Obviously the higher performance timer used by SYSDATETIME() on Windows Server 2008 is not available on Windows XP.

Conclusion

This article demonstrated the limitations of using internal time functions provided by SQL Server. Very few applications may actually need such high precision timekeeping but understanding the limitations of tools we use is always helpful before they become a problem. Unfortunately there are no good options for overcoming the limitations of the time keeping functions mentioned in this article.

Using a client to provide time values works only if all the clients are synchronized with a greater precision then provided by SQL Server functions. In real world it would only work if there was only one client machine. Another option would be to create an extended stored procedure or to use CLR based function using high precision timers available in the .NET Framework. This might work if the server processor(s) operated at high clock speeds because of the high cost associated with invoking those methods.