InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 18 Jun 2013 18:55:56 GMT20http://www.sqlservercentral.com/Forums/Topic920128-1494-1.aspxMy contribution to the body of data:Dell Inspiron 9400 laptopIntel Core2 T5600 CPU4GB RAMWindows 7 Enterprise SP1 64-bitMicrosoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) Jun 17 2011 00:54:03 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)Query results:51383.33982CURRENT_TIMESTAMP seems to work in 2008 R2:SELECT CURRENT_TIMESTAMP-----------------------2012-02-04 09:06:08.757SELECT CURRENT_DATE Incorrect syntax near the keyword 'CURRENT_DATE'.Sat, 04 Feb 2012 07:14:47 GMTPeter Maloofhttp://www.sqlservercentral.com/Forums/Topic920128-1494-1.aspxDon't think I've seen this configuration yet:SQL 2008 SP2 on an [b]Itanium[/b] 64Windows 2003 SP21st test-132273 (wow) 8.02182A test machine with nothing going on but a completely full data drive (shouldn't matter)No 2nd test 'casue the 1st took almost 18 minutes to run :w00t:Fri, 03 Feb 2012 15:03:35 GMTken.trockhttp://www.sqlservercentral.com/Forums/Topic920128-1494-1.aspxWell I tried running sysdatetime on these 2 servers and wondered about the results.So I modified my query to dump the results into a table so I could see better. The results surprised me.[code="sql"]DECLARE @TimeStart DATETIME2DECLARE @TimeEnd DATETIME2DECLARE @Count INTDeclare @Duration intDECLARE @I INTDeclare @results table(Duration int, Iterations int, StartTime datetime2, EndTime datetime2)SET @I = 0Set @Duration = 0WHILE @I < 10000BEGIN SET @TimeStart = sysdatetime() SET @TimeEnd = @TimeStart Set @Count = 0 -- Find the amount of time it takes to get a different value -- To get strictly time, comment out the Set @Count line WHILE @TimeStart = @TimeEnd BEGIN SET @TimeEnd = sysdatetime() Set @Count = @Count + 1 End Set @Duration = DATEDIFF(MICROSECOND, @TimeStart, @TimeEnd) Set @I = @I + 1 Insert into @results(Duration, Iterations, StartTime, EndTime) values (@Duration, @Count, @TimeStart, @TimeEnd) Endselect Duration , Iterations , StartTime , EndTime from @resultsSelect AVG(duration)From @resultsselect Duration , count(Duration)from @resultsgroup by Duration[/code]The slower machine gave this:Duration Iterations StartTime EndTime977 51 2012-02-03 14:37:30.7926123 2012-02-03 14:37:30.7935890976 200 2012-02-03 14:37:30.7935890 2012-02-03 14:37:30.7945657977 267 2012-02-03 14:37:30.7945657 2012-02-03 14:37:30.7955424977 265 2012-02-03 14:37:30.7955424 2012-02-03 14:37:30.7965191. . .Duration (No column name)976 2960977 69111953 131954 122930 322931 23906 13907 15... and 48 others with a longer duration up to 620204The faster machine gave this:Duration Iterations StartTime EndTime1000 63 2012-02-03 14:38:58.9925551 2012-02-03 14:38:58.99355511000 347 2012-02-03 14:38:58.9935551 2012-02-03 14:38:58.99455511000 423 2012-02-03 14:38:58.9945551 2012-02-03 14:38:58.99555511000 449 2012-02-03 14:38:58.9955551 2012-02-03 14:38:58.9965551. . .All of the times ended in 5551.Duration (No column name)1000 97133000 1524000 1317000 1300000 2304000 1Fri, 03 Feb 2012 13:36:51 GMTdcradayhttp://www.sqlservercentral.com/Forums/Topic920128-1494-1.aspxI, too, ran this code on one server and was getting 3 milliseconds. I was curious as to the duration of the test and the inner workings of the code so I wrote this to remove the code from the loop and check on a few things. I found that the results are highly dependent on the hardware configurations.[code="sql"]DECLARE @TimeStart DATETIMEDECLARE @Time DATETIMEDECLARE @TimeEnd DATETIMEDeclare @TotalTime intDECLARE @Count INTDeclare @TotalCount intDeclare @HighCount intDeclare @LowCount intDeclare @HighTime intDeclare @LowTime intDeclare @IterTime intDeclare @OddTime intDECLARE @I INTDeclare @TestStart datetimeDeclare @TestEnd dateTimeSET @I = 0Set @TotalTime = 0Set @TotalCount = 0Set @HighCount = 0Set @LowCount = 99999999Set @HighTime = 0Set @LowTime = 99999999Set @IterTime = 0Set @OddTime = 0Set @TestStart = GETDATE()WHILE @I < 10000BEGIN SET @TimeStart = GETDATE() SET @TimeEnd = @TimeStart Set @Count = 0 -- Find the amount of time it takes to get a different value -- To get strictly time, comment out the Set @Count line WHILE @TimeStart = @TimeEnd BEGIN SET @TimeEnd = GETDATE() Set @Count = @Count + 1 End Set @IterTime = DATEDIFF(MILLISECOND, @TimeStart, @TimeEnd) Set @TotalTime = @TotalTime + @IterTime Set @TotalCount = @TotalCount + @Count Set @I = @I + 1 If @Count < @LowCount Set @LowCount = @Count If @Count > @HighCount Set @HighCount = @Count If @IterTime < @LowTime Set @LowTime = @IterTime If @IterTime > @HighTime Set @HighTime = @IterTime-- Why 3? Because I ran the inner loop 100 times while building this and it was nearly always returning 3-- If @IterTime <> 3 Set @OddTime = @OddTime + 1 EndSet @TestEnd = GETDATE()Print 'Test Duration'print DATEDIFF(MILLISECOND, @teststart, @testend) / 1000print '---------------------------'print 'Average Iterations' print @TotalCount / @Iprint 'High Iterations'print @HighCountprint 'Low Iterations'print @LowCountprint '-----------------------'print 'Average Time'print @TotalTime / @Iprint 'High Time'print @HighTimeprint 'Low Time'print @LowTimeprint 'Odd Time Count'print @OddTime[/code]When I ran this on a machine with NT6.1 x64, 40 gigs of memory, and 16 processors I got these results:Test Duration37---------------------------Average Iteratations1017High Iterations2301Low Iterations2-----------------------Average Time3High Time306Low Time3Odd Time Count115 So 115 times out of 10,000 the result wasn't 3 milliseconds.On a machine (a sandbox) NT 6.0 x86 with 4 gigs of memory and 1 processor I got these results:Test Duration182---------------------------Average Iteratations4339High Iterations6284Low Iterations25-----------------------Average Time17High Time483Low Time3Odd Time Count9999Fri, 03 Feb 2012 10:13:24 GMTdcradayhttp://www.sqlservercentral.com/Forums/Topic920128-1494-1.aspxInteresting exercise, but keep in mind that for most of us this is academic since most server clocks are not thermally compensated and easily drift by a mS every couple of minutes (or when a fan kicks on).So unless you have a real network clock (symmetricon etc), all those decimal digits are meaningless.Fri, 03 Feb 2012 08:22:59 GMTjay-hhttp://www.sqlservercentral.com/Forums/Topic920128-1494-1.aspxand the more precise datetime2 test gives:183671034.88John BirchFri, 03 Feb 2012 02:45:46 GMTjohn-645829http://www.sqlservercentral.com/Forums/Topic920128-1494-1.aspxUsing Windows 7 Enterprise, 64-bit OSIntel Core i7 CPU Q720 @1.60 GHz 1.60 GHz4GB memoryand SQL Server 2008 using the GetDate() code...I get 41793.60995and when I run the same code in a stored procedure, I get 41263.41638Fri, 03 Feb 2012 02:40:21 GMTjohn-645829http://www.sqlservercentral.com/Forums/Topic920128-1494-1.aspxVery interesting.[code="sql"]37503.336Microsoft 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)[/code]Big load going on with my 2008 server, so results are probably whacky.[code="sql"]--GETDATE70015.6286--SYSDATETIME96315625Microsoft 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)[/code]Thu, 08 Jul 2010 03:37:54 GMTCadavrehttp://www.sqlservercentral.com/Forums/Topic920128-1494-1.aspxThnaks:-) Know the precision for the time function GETDATE(), wonderful ideas for future precision using.Wed, 26 May 2010 22:14:02 GMTchangblueskyhttp://www.sqlservercentral.com/Forums/Topic920128-1494-1.aspx[quote][b]Jeff Moden (5/12/2010)[/b][hr][quote][b]Joe Celko (5/12/2010)[/b][hr]I just wish that Microsoft has used the ISO/ANSI Standard CURRENT_TIMESTAMP and CURRENT_DATE instead of more dialect.[/quote]I haven't checked the other versions of SQL Server but SELECT CURRENT_TIMESTAMP works just fine in 2k5.[/quote]Works fine in 2k8 (eval copy) as well.Tue, 18 May 2010 04:15:40 GMTBrandie Tarvinhttp://www.sqlservercentral.com/Forums/Topic920128-1494-1.aspxi run the query in my server with 2000 as OS and sql server 2005 sp2, the result is 57813.33333and in my local system it with windows XP and sql server 2005 sp2 the results is like 71963.43524Sun, 16 May 2010 22:53:28 GMTsharath.chalamgarihttp://www.sqlservercentral.com/Forums/Topic920128-1494-1.aspxI 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.Fri, 14 May 2010 13:01:35 GMTJacekOhttp://www.sqlservercentral.com/Forums/Topic920128-1494-1.aspxWhen 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)167183.34807loop getdate 55973 Milliseconds duration-- start up 5 big, ugly queries493695.01339loop getdate 247506 Milliseconds durationFri, 14 May 2010 10:06:01 GMTCarla Wilson-484785http://www.sqlservercentral.com/Forums/Topic920128-1494-1.aspxLike others I've found that SYSDATETIME is not as accurate as GETDATE.My results:H/W Xeon quad-core 2.83GHz, 2GB ram, SSD raidS/W Win2k3 R2 x64, SQL 2008 developer (not R2)GETDATE : count 19329, time 3.33385 elapsed 1:04CURRENT_TIMESTAMP: count 19345, time 3.33404 elapsed 1:04SYSDATETIME: count 4175, time 15.6525, elapsed 1.05;-)Thu, 13 May 2010 06:08:18 GMTmartyn.bateshttp://www.sqlservercentral.com/Forums/Topic920128-1494-1.aspx[quote][b]Jeff Moden (5/12/2010)[/b][hr][quote][b]Joe Celko (5/12/2010)[/b][hr]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 :-)[/quote]I haven't checked the other versions of SQL Server but SELECT CURRENT_TIMESTAMP works just fine in 2k5.[/quote]I works in 2000 as well. Anyone have 7.0 or 6.5 to test?Wed, 12 May 2010 16:22:40 GMTTrey Stakerhttp://www.sqlservercentral.com/Forums/Topic920128-1494-1.aspx[quote][b]Joe Celko (5/12/2010)[/b][hr]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 :-)[/quote]I haven't checked the other versions of SQL Server but SELECT CURRENT_TIMESTAMP works just fine in 2k5.Wed, 12 May 2010 15:55:14 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic920128-1494-1.aspx[b]Thank you for such a nice, informative article! [/b]This is why this site is so useful - we can learn from others and not have to try and duplicate everything ourselves. If we needed more resolution (e.g. microseconds) has anyone checked the date/time functions in the programming languages and used CLR to access those times?Wed, 12 May 2010 14:44:05 GMTKim Claybaughhttp://www.sqlservercentral.com/Forums/Topic920128-1494-1.aspxThanks for the nice article.Wed, 12 May 2010 12:09:57 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic920128-1494-1.aspx[quote][b]shane.vincent (5/12/2010)[/b][hr]Just a thought, but I also got a more accurate GETDATE return value than the original article.(19430 ,3.52769) I think that a lot of this has to do with what else is running on the SQL box.So the date accuracy will be dependent on the load of the server to a degree.At least that is what it seems like.[/quote]Only the first number (how many times the value changed) is affected by the load because the overall duration of the query is going to be affected. The higher the load on the box the higher the first number is going to be. But the second number (the "precision" number) does not really change that much with the load - just a fraction of a percent.Most people concentrated on the getdate() values but I think when you look at the sysdatetime() the difference between what the data type can store and the precision of what the function can return is much more dramatic. With getdate() we are talking of a difference of ~16ms (for many of us) vs ~3ms which is just about 5 times. When you look at the systdatetime() of about 1ms vs the 100ns we are talking 10000 times.Wed, 12 May 2010 12:00:30 GMTJacekOhttp://www.sqlservercentral.com/Forums/Topic920128-1494-1.aspx[quote][b]thisisfutile (5/12/2010)[/b][hr][quote][b]thisisfutile (5/12/2010)[/b][hr][quote][b]JacekO (5/12/2010)[/b][hr]If anyone of you got the 3.33 ms on a 32 bit box please reply with more specifics on the OS, CPU and SQL Server version.[/quote]My XP workstation is 32-bit with SQL Server 2008 Express (SP3) ... and I get 3.3333 on the first queryMy server is SBS 2003, 32-bit with SQL Server 2005 Workgroup (SP2) ... and I get 15.6xxx on the first queryHTH[/quote]Expanding on my previous respons...If I run the first query directly on the server, it still reports 15.62xx. After seeing 3.33 on my workstation and it's local SQL install, I sort of expected to see 3.33 when running the query directly on the server. </naivety>:P[/quote]I think you might be actually pointing out to the reason of the differing results. Your server is running SP2 and workstation is running SP3.The link Chris posted to the MS blog (see my reply above) indicates that MS introduced some changes in SP3 of 2005 so the server can use a higher precision timers. If the timer has better presision then 3.33 ms then the getdate will be limited by the datatype precision. If the server is using the lower precision timer then you will see the ~16ms getdate() precision.Wed, 12 May 2010 11:47:16 GMTJacekOhttp://www.sqlservercentral.com/Forums/Topic920128-1494-1.aspx[quote][b]Chris Howarth-536003 (5/12/2010)[/b][hr]This makes an interesting read, particularly regarding SQL 2005 SP3's attempts to lower granularity to 1ms, but the article doesn't specifically mention GETDATE():[url=During startup SQL Server attempts to lower the granularity to 1ms.]http://blogs.msdn.com/psssql/archive/2009/05/29/how-it-works-sql-server-timings-and-timer-output-gettickcount-timegettime-queryperformancecounter-rdtsc.aspx[/url]Chris[/quote]Chris, good link, and it basically describes the underlying problems that cause the ~16ms or 3.3 ms for getdate() and no better precision then 1 ms for sysdatatetime(). I guess some OS configurations expose the higher percision timer to SQL Server so this is why some people come with the 3.33ms for getdate(). It might be because of SP versions on the OS or SQL Sevrer and also the boot.ini switch. There is some indication in the article that Microsoft recently introduced some means of exposing a higher precision timers to the SQL Sevrer with the new service packs. That might explain why some people can get better results then the ~16ms I got in my tests.Most precision timers under Windows are tick counters and are good at calculating the time difference - not the current time. The getdate() and sysdatetime() functions are only as precise as the underlying OS timer.Wed, 12 May 2010 11:29:11 GMTJacekOhttp://www.sqlservercentral.com/Forums/Topic920128-1494-1.aspxWin Xp 32 bit SP3 (Under VMware Workstation)SQL Server 2008 (10.0.2531)Getdate(): 4291 values with precision 3.35027SysDateTime(): 1903 values with precision 10.0641Wed, 12 May 2010 11:00:13 GMTrsaxer@cox.nethttp://www.sqlservercentral.com/Forums/Topic920128-1494-1.aspxHi there,Not that it really matters, but if i use the time difference every time getdate() returns a differente value, i reach to a better result.[code="sql"]DECLARE @TimeStart DATETIMEDECLARE @Time DATETIMEDECLARE @TimeEnd DATETIMEDECLARE @I INTDECLARE @Count INTDECLARE @Difference INTSET @I = 0SET @Count = 0SET @Difference = 0SET @TimeStart = GETDATE()SET @Time = @TimeStartWHILE @I < 10000000BEGIN SET @TimeEnd = GETDATE() IF @TimeEnd <> @Time BEGIN SET @Count = @Count + 1 SET @Difference = @Difference + DATEDIFF(millisecond, @Time, @TimeEnd) SET @Time = @TimeEnd END SET @I = @I + 1ENDPRINT @CountPRINT DATEDIFF(millisecond, @TimeStart, @TimeEnd) / CAST(@Count AS REAL)PRINT @Difference / CAST(@Count AS REAL)[/code]With this code i get the following result on my SQL2005 instance:34783.333243José CruzWed, 12 May 2010 10:59:57 GMTJosé.Cruzhttp://www.sqlservercentral.com/Forums/Topic920128-1494-1.aspxJust a thought, but I also got a more accurate GETDATE return value than the original article.(19430 ,3.52769) I think that a lot of this has to do with what else is running on the SQL box.So the date accuracy will be dependent on the load of the server to a degree.At least that is what it seems like.Wed, 12 May 2010 10:33:45 GMTshane.vincenthttp://www.sqlservercentral.com/Forums/Topic920128-1494-1.aspxIf someone asked me about the accuracy in an interview, my response would be "I don't know. I've never needed GetDate() or related functions to be that precise. If and when I do need to know, I'd look it up in Books Online."I'd probably be dinged in the interview, but at least I wouldn't be taking potshots and getting them wrong. @=)Wed, 12 May 2010 10:33:44 GMTBrandie Tarvinhttp://www.sqlservercentral.com/Forums/Topic920128-1494-1.aspxWindows 2008SQL Server Enterprise 64bit 2008 sp1Getdate /2nd run 88047 /1133054.8 /7.44SysDateTime127186 /1373773476.36 /6143.44Windows 2003SQL Server Enterprise 2008 sp1GetDate /2nd run19830 /365283.367 /3.464SysdateTime127186 /84373476.36 /15636Perhaps the measurements are affected by load on the server. I also notice that there is a CPU and network load on my client workstation from SSMS while the queries are running, could the client also be a factor?Wed, 12 May 2010 10:26:07 GMTvincent.mielehttp://www.sqlservercentral.com/Forums/Topic920128-1494-1.aspxI 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 :-)Wed, 12 May 2010 10:07:44 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic920128-1494-1.aspxHi there,Windows 7 Professional, 32-bit operatibg system.Microsoft SQL Server 2005 - 9.00.4053.00 (Intel X86) May 26 2009 14:24:20 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 6.1 (Build 7600: ) 34683.33333Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86) Mar 29 2009 10:27:29 Copyright (c) 1988-2008 Microsoft Corporation Developer Edition on Windows NT 6.1 <X86> (Build 7600: ) - getdate()37853.33316-sysdatetime()225621000.77José CruzWed, 12 May 2010 09:55:40 GMTJosé.Cruzhttp://www.sqlservercentral.com/Forums/Topic920128-1494-1.aspxNope: Ran this on my 32-bit box and got:86503.34139[u]Specs[/u]: CPU: Xeon E5450 @ 3 GHzOS: 2003 Server, Standard Ed., R2, SP2SQL: Microsoft SQL Server 2005 - 9.00.4053.00 (Intel X86) May 26 2009 14:24:20 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2) Guess you'll have to re-apply for a job elsewhere now ;-)Let us know when you've figured out the rationale for this varying result.....RichWed, 12 May 2010 08:18:37 GMTrmechaberhttp://www.sqlservercentral.com/Forums/Topic920128-1494-1.aspxI don't know what is all this fuzz about ... The results are going to be very server-dependent you don't know what other activities are happening at the same time you are running these loops. In my opinion these tests really represent nothing.Wed, 12 May 2010 08:12:59 GMTnoeldhttp://www.sqlservercentral.com/Forums/Topic920128-1494-1.aspxOf course I meant systemdatetime()!Wed, 12 May 2010 08:10:59 GMTAlastair Marshallhttp://www.sqlservercentral.com/Forums/Topic920128-1494-1.aspxI think the article is referring to the ACCURACY of the two functions, not to the precision.Wed, 12 May 2010 08:10:43 GMTgitmohttp://www.sqlservercentral.com/Forums/Topic920128-1494-1.aspx... Soooo how do we notify the article author that he was wrong?Wed, 12 May 2010 08:09:52 GMTHobbes-230252http://www.sqlservercentral.com/Forums/Topic920128-1494-1.aspxThank you for the information we don't for the moment use the precision described in our day to day processing of data, but it is definitely something to keep in mind for the future.Wed, 12 May 2010 08:07:39 GMTbluelightning1http://www.sqlservercentral.com/Forums/Topic920128-1494-1.aspxOf course, the returned values will vary here because this is not factoring in the execution time of the getdate() or systemgetdate() functions. They will each have a cost associated with each call, so the answer in this logic isn't so much just a question of the accuracy of the functions, but also the processing time for these calls. These will vary depending on the hardware of the machine that you are on and other activity on the system.Wed, 12 May 2010 08:04:16 GMTAlastair Marshallhttp://www.sqlservercentral.com/Forums/Topic920128-1494-1.aspxThis makes an interesting read, particularly regarding SQL 2005 SP3's attempts to lower granularity to 1ms, but the article doesn't specifically mention GETDATE():[url=During startup SQL Server attempts to lower the granularity to 1ms.]http://blogs.msdn.com/psssql/archive/2009/05/29/how-it-works-sql-server-timings-and-timer-output-gettickcount-timegettime-queryperformancecounter-rdtsc.aspx[/url]ChrisWed, 12 May 2010 08:00:58 GMTChris Howarth-536003http://www.sqlservercentral.com/Forums/Topic920128-1494-1.aspxNow I am really puzzled. Well, the whole thing has to do with the OS timer. When I bumped into the 16 ms precision of getdate() over a year ago I did some research on the internet. I could not find anything specific to SQL Server but some articles pointed out that it is possible to alter the frequency of the system timer by running multimedia software. In order to test the 16 ms precision I even created a .NET app that run a code similar to the SQL query (same logic) and I never could brake the 16 ms barrier.I was never successful in altering the system timer, but based on the results of all your queries it looks like it is possible. Now, the $150000 question is: what is that thing that makes the difference?Wed, 12 May 2010 07:52:29 GMTJacekOhttp://www.sqlservercentral.com/Forums/Topic920128-1494-1.aspx[quote][b]thisisfutile (5/12/2010)[/b][hr][quote][b]JacekO (5/12/2010)[/b][hr]If anyone of you got the 3.33 ms on a 32 bit box please reply with more specifics on the OS, CPU and SQL Server version.[/quote]My XP workstation is 32-bit with SQL Server 2008 Express (SP3) ... and I get 3.3333 on the first queryMy server is SBS 2003, 32-bit with SQL Server 2005 Workgroup (SP2) ... and I get 15.6xxx on the first queryHTH[/quote]Expanding on my previous respons...If I run the first query directly on the server, it still reports 15.62xx. After seeing 3.33 on my workstation and it's local SQL install, I sort of expected to see 3.33 when running the query directly on the server. </naivety>:PWed, 12 May 2010 07:51:58 GMTthisisfutilehttp://www.sqlservercentral.com/Forums/Topic920128-1494-1.aspxHere is mineSQLServer 2000 on XP SP3 running locally 3596 : 15.7664SQLServer 2005 on Server 2008 via Terminal Server 2815 : 3.33321SQLServer 2005 on Vista from Server 2008 via Terminal Server 108619 : 4.07807All SQLServers are developer versions.Should not matter but I point out that I terminal servered in to Server 2008 from the top XP machine and ran the query. Then in that terminal sesion connected to the Vista SQLServer and ran again for the third result.Wed, 12 May 2010 07:44:52 GMTbill page-320204http://www.sqlservercentral.com/Forums/Topic920128-1494-1.aspxTurns out that by repeatedly running GETDATE() as described in the article, all you're doing is measuring the clock ticks that the machine you're running on is capable of.On some machines I tested (64 bit or otherwise) it's 16ms and on other machines it is 3ms, or possibly less - but of course, GETDATE() cannot resolve any ticks below 3ms.So, GETDATE() (or rather, the datetime datatype) has a precision of 3.33ms - but whether you can illustrate this using GETDATE() is dependant on the spec of the machine you're on.Check out this article, specifically the Sidebar near the bottom. [url=http://msdn.microsoft.com/en-us/library/aa175784(SQL.80).aspx]http://msdn.microsoft.com/en-us/library/aa175784(SQL.80).aspx[/url]Wed, 12 May 2010 07:44:52 GMTSQLZ