Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Do you know your getdate()?

By Jacek Osuchowski, (first published: 2010/05/12)

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.

Resources:

GETDATE.doc
Total article views: 27277 | Views in the last 30 days: 6
 
Related Articles
FORUM

help using getdate function

help using getdate function

FORUM

Count function issue in MDX in SQL Server 2008 R2

Count function issue in MDX in SQL Server 2008 R2

FORUM

Count Function

Count Function with condition

FORUM

GetDate Function

Getdate() as default

FORUM
Tags
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones