THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Andrew Kelly

What time do you have?

The way in which SQL Server has treated Time has been a very misunderstood subject for as long as I can remember. The largest part of that was the fact that SQL Server was only accurate to 3.33ms. I would like to point you to an excellent article on this and other details surrounding Datetime in SQL Server by Tibor Karaszi found here:  http://www.karaszi.com/SQLServer/info_datetime.asp  Well now with the release of SP3 for SQL Server 2005 (and of course SQL 2008 as well) SQL Server no longer uses the inaccurate techniques associated with the RDTSC component of Windows. Since there is a lot to explain about how this all works I will again point you to another link which goes into much greater detail.  http://blogs.msdn.com/psssql/archive/2008/12/16/how-it-works-sql-server-no-longer-uses-rdtsc-for-timings-in-sql-2008-and-sql-2005-service-pack-3-sp3.aspx  I for one say it is about time:).

Published Saturday, December 20, 2008 9:12 AM by Andrew Kelly

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Gorm Braarvig said:

If you want a solution for 2005: SqlMeasureTime at codeplex does it.

December 20, 2008 11:29 AM
 

Adam Machanic said:

Gorm: No, it doesn't.  The point here is that every component of SQL Server 2005 SP2 and back used an inaccurate timing mechanism which caused a variety of problems throughout the engine--not just in user-defined code.  That entire subsystem has been replaced in 2005 SP3 and 2008.

December 21, 2008 1:23 PM
 

Linchi Shea said:

It's quite unfortunate that the extent and the detailed nature of the timer-related problem wasn't really well documented.

December 21, 2008 6:22 PM
 

Gorm Braarvig said:

Adam: No, I see what you mean, I was looking at it with completely different eyes: how I can use time myself. Sorry for the noice.

Looks to me like they already use QueryPerformanceCounter(http://support.microsoft.com/kb/931279), and that this is part of the problem!

December 24, 2008 6:47 AM
 

Ted said:

I'd be happy with a real time construction built in function for TSQL instead of constructing times from text strings.

GETDATE(year, month, day, hour, minute, second, milisecond) where all parameters are integers and a date is returned

This is a huge omission from the ANSI SQL standard and greatly reduces portability of SQL scripts

December 30, 2008 11:50 AM
 

Alfred Yomtov said:

Or just recall that "time is money", :)

Really, plainly think of date/time in terms of numbers.

http://code.msdn.microsoft.com/sqldt

February 16, 2009 4:13 AM

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement