SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Do you know your getdate()?


Do you know your getdate()?

Author
Message
Trey Staker
Trey Staker
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1624 Visits: 2788
Jeff Moden (5/12/2010)
Joe Celko (5/12/2010)
I just wish that Microsoft has used the ISO/ANSI Standard CURRENT_TIMESTAMP and CURRENT_DATE instead of more dialect. Of course, they are going to have trouble with TIMESTAMP(n) thanks to the old Sybase Code Museum :-)


I haven't checked the other versions of SQL Server but SELECT CURRENT_TIMESTAMP works just fine in 2k5.


I works in 2000 as well. Anyone have 7.0 or 6.5 to test?

---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum
martyn.bates
martyn.bates
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 37
Like others I've found that SYSDATETIME is not as accurate as GETDATE.
My results:
H/W Xeon quad-core 2.83GHz, 2GB ram, SSD raid
S/W Win2k3 R2 x64, SQL 2008 developer (not R2)

GETDATE : count 19329, time 3.33385 elapsed 1:04
CURRENT_TIMESTAMP: count 19345, time 3.33404 elapsed 1:04
SYSDATETIME: count 4175, time 15.6525, elapsed 1.05
;-)
Carla Wilson-484785
Carla Wilson-484785
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2294 Visits: 1951
When I first read through the code, my first thought was that load on the system would impact the calculations. So I did a quick and dirty test, taking advantage of a big, ugly query I had. I added a timer to the whole getdate loop and ran with no other queries running. Then I started up 5 independent queries and ran the getdate loop. The load definitely impacted the calculation. (Note: this was run on SQL 2005 server.)

-- test impact of load on getdate() calculation.
-- (normal load on system)
16718
3.34807
loop getdate 55973 Milliseconds duration

-- start up 5 big, ugly queries
49369
5.01339
loop getdate 247506 Milliseconds duration
JacekO
JacekO
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1605 Visits: 616
I would like to thank everybody who took the time to read the article and all the respondents who provided their input on this forum. I don’t think I can answer all the posts individually but I will try to answer the subjects brought up the most frequently.

The Interview – first of all it was just a ‘teaser’ to get you into reading the article. If you carefully read the hypothetical answer presented in the article, then you would realize that this answer includes a logical deduction that was flawed. The flaw lays in the fact that the answer ties the getdate() and sysdatetime() precision to the underlying datatype. The article tries to explain that the precision of such functions is dependent on OS timers available to the SQL Server. Many of you pointed out that I was wrong because they got 3.33 ms on getdate() on their system. Well, good for you. You got a SQL Server version (including SP) and a OS version combination that allows the getdate() to be more precise or accurate (some argued about the term I should have used). But there are plenty of responses with the ~16 ms precisions on getdate() or sysdatetime() to prove that I did not made this whole thing up. And no one got even remotely close to the 100 ns on the sysdatetime() function…

I was not really surprised that there were people who got 3.33 ms on the getdate(). I was kind of disappointed that I could not find any of the systems around here to achive this kind of precision. Well, let’s blame it on the corporate standardization – the pool of available systems was not diversified enough. What really surprised me, was that people were reporting worst performance of sysdatettime() vs getdate() on the same system.

OK, let’s look at the load vs precision aspect. Again – this is related to the fact that Windows is not a real time OS. So, if you really starve the processor by taxing it at 100% non stop for an extended period of time it, will ‘skip’ or ‘slowdown’ some of the OS functions and the timers will not get updated every time they should. The duration of the queries will vary depending on the load but I think if you keep the load reasonable, the 20% or 90% load on the CPU will not affect the precision of the time functions much.

Someone mentioned that if they needed to know the answer to the ‘interview’ question, the Books Online would be the place to get an answer. Well, I don’t think this particular question does have an answer in the Books Online and if all the answers to all SQL Server questions could be found in Books Online then we would not need such a great place as SQLServerCentral.com. Would we?

PS.
If you are not sick and tired of this subject…
Anyone who posted your results could you please provide your SQL version including SP. And maybe OS version, including SP as well.
Thanks. I am still trying to figure out if there is a logic to this behavior.

---------------------------------------------
Nothing is impossible.
It is just a matter of time and money.

sharath.chalamgari
sharath.chalamgari
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2020 Visits: 798
i run the query in my server with 2000 as OS and sql server 2005 sp2, the result is

5781
3.33333

and in my local system it with windows XP and sql server 2005 sp2 the results is like

7196
3.43524
Brandie Tarvin
Brandie Tarvin
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39192 Visits: 9291
Jeff Moden (5/12/2010)
Joe Celko (5/12/2010)
I just wish that Microsoft has used the ISO/ANSI Standard CURRENT_TIMESTAMP and CURRENT_DATE instead of more dialect.


I haven't checked the other versions of SQL Server but SELECT CURRENT_TIMESTAMP works just fine in 2k5.


Works fine in 2k8 (eval copy) as well.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
changbluesky
changbluesky
SSC Veteran
SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)

Group: General Forum Members
Points: 213 Visits: 150
Thnaks:-) Know the precision for the time function GETDATE(), wonderful ideas for future precision using.
Cadavre
Cadavre
SSCrazy Eights
SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)

Group: General Forum Members
Points: 9356 Visits: 8492
Very interesting.

3750
3.336
Microsoft SQL Server 2005 - 9.00.4028.00 (Intel X86)
Oct 20 2008 19:45:04
Copyright (c) 1988-2005 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)



Big load going on with my 2008 server, so results are probably whacky.

--GETDATE
700
15.6286
--SYSDATETIME
963
15625
Microsoft SQL Server 2008 (RTM) - 10.0.1763.0 (X64)
Sep 18 2008 20:59:12
Copyright (c) 1988-2008 Microsoft Corporation
Developer Edition (64-bit) on Windows NT 5.2 <X64> (Build 3790: Service Pack 2)




Forever trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


Craig Wilkinson - Software Engineer
LinkedIn
john-645829
john-645829
Valued Member
Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)

Group: General Forum Members
Points: 73 Visits: 12
Using Windows 7 Enterprise, 64-bit OS
Intel Core i7 CPU Q720 @1.60 GHz 1.60 GHz
4GB memory

and SQL Server 2008

using the GetDate() code...

I get
4179
3.60995

and when I run the same code in a stored procedure, I get
4126
3.41638
john-645829
john-645829
Valued Member
Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)

Group: General Forum Members
Points: 73 Visits: 12
and the more precise datetime2 test gives:

18367
1034.88


John Birch
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search