November 9, 2012 at 8:06 am
I took me twenty minutes of reading BoL and head scratching... Thanks, Tom!
November 9, 2012 at 9:59 am
Would method 1 be reliable even in the early morning hours? As SYSDATETIME() is non-deterministic, could it not use two slightly different values for the two calls?
I get the feeling that you, Tom, considered that and have an explanation of the definition of "deterministic."
November 9, 2012 at 1:16 pm
john.arnott (11/9/2012)
Would method 1 be reliable even in the early morning hours? As SYSDATETIME() is non-deterministic, could it not use two slightly different values for the two calls?I get the feeling that you, Tom, considered that and have an explanation of the definition of "deterministic."
Actually, you give me too much credit - I didn't even consider that for method 1 (not sure whether that was because I was counting it as unreliable anyway or because I didn't think of it - it's a while since I submitted the question).
I did consider it for method 4, because I think I read somewhere that CTEs generally work by text substitution, so that if the same CTE is referred to more than once in a query the two instances may deliver different values. Of course the optimizer may decide to common up textually equal expressions, provided that doing so doesn't screw up store management, but I don't know whether it would do that or not; and of course recomputing the content of a CTE could lead to some new variations of queries whose semantics could be altered according to whether intermediate results are spooled or not (so if it's an update query, new instances of the Harlequin Problem - of course according to some people where insert and delete queries have their semantics modified in a similar manner that would be the Harlequin Problem too) so it's sometimes essential to spool a CTE. Spooling every CTE would be a performance nightmare, so that presumably doesn't happen. All that leaves me non the wiser about whether this particular query will have a problem, so I decided to test method 4 fairly thoroughly. On my machine, with a reasonable amount of background work going on, I invoked this method 10000 times in a loop to write a row into a table with the time and the nanoseconds since midnight (the final select of method 4 was replace by
insert #pig select (1000000000 * CAST(secs.ss as bigint)+CAST(datediff(NS,secstart.start,t) as bigint)) , t
from secs cross join dy cross join secstart cross join noww;
to do this - for some reason in my notes for the test all occurrences of now have two ws, presumably I wanted to avoid the reserved [???] word) and then counting the number of rows where the nsec column was the same as the nanosec count calculated by method 2 from the t column), and also counting the number of rows where it was different, delivered counts of 10000 for same, and 0 for different. This was repeated at several times of day and with several different background workloads added (AV and spyware scan, database backups, intense numeric stuff, massive automatic text edits, indexing files - , network dowloads, varios combinations of these) with the same results.
What that tells me is that on my machine either the optimiser decides to calculate that CTE just once, or the time difference between the two times it calculates it is always less that 100NS, and I think the latter implies that the query (up to the point of having the output ready to write to the log) takes less time than the minimum interval (if there is such a thing in this verson of NT) that a thread is given when selected before it will be interupted. So for me method 4 is reliable. The scary thing is that I have no real proof that it is actually reliable on any other machine, or that it will be reliable in SQLK 2012 (I'm using 2008 R2), or that it will be reliable if I substitute some version of Windows 7 for Windows XP Pro. Despite that, I'm sure that it is indeed reliable, at least in SQL 2008 R2.
Of course this risk can be eliminated completely by having the query work off a variable previously initialised to sysdatetime(), and that applies equally to method 1 (for the few hours each day that it works), so these methods can be made safe (for method 1, safe for a few hours per day) by converting them into two statements each instead of 1. But of course I prefer method 2 to method 4, because it's simpler and even in its single statement form doesn't carry this risk.
edit: Why avoid "now" as a reserved word? It isn't a reserved word in T-SQL. I must have been thinking of some other language (maybe ISO SQL - it has more reserved words than T-SQL - but I don't think so).
Tom
November 13, 2012 at 2:43 pm
Koen Verbeeck (11/8/2012)
sanket kokane (11/7/2012)
I got it wrong ...you should mention sql server Version in question .
Sysdatetime () not present in sql 2000 and 2005
So, for which versions do you think the question is meant? Obviously for 2008 and up. There's also a consensus that versions who are no longer supported by Microsoft do not count for Questions of the Day.
+1
Also, if you are testing on an old version no longer under support then at that point you should include version.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 16, 2012 at 5:09 am
I personally think the answer should be 'none of the above'. The accuracy of sysdatetime() depends on the computer hardware and version of Windows on which the instance of SQL Server is running. Queries 2 and 4 may give an accurate value for the number of nanoseconds since when your server believes midnight to have been but not 'reliably' when midnight actually was unless you are confident that the date time of your server is accurate to 100 nanoseconds.
November 16, 2012 at 5:58 am
seankyleprice (11/16/2012)
I personally think the answer should be 'none of the above'. The accuracy of sysdatetime() depends on the computer hardware and version of Windows on which the instance of SQL Server is running. Queries 2 and 4 may give an accurate value for the number of nanoseconds since when your server believes midnight to have been but not 'reliably' when midnight actually was unless you are confident that the date time of your server is accurate to 100 nanoseconds.
Excellent point.
We can of course take it that the time referred to as midnight in the context of how long has the computer been going since midnight as being the time when it thought midnight was, but that doesn't help because reliability is still affected by the clock drift between then and now.
Typical systems today, even those with very good hardware, despite usually having excellent clock management software that (when correctly configured, which may not be all that common) uses data from a reliable time server to determine how much drift there is in the hardware would not be able to run reliably with a drift rate between time server inputs of less that 37 microseconds per annum (that's a few decimal orders of magnitude better than typical, I think), so if "reliable to 100 nanoseconds" really means just that you are right and the correct answer is "none of the above". Even if "reliable to the nearest 100 nanoseconds" means "accurate count of the clock's nominally 100 ns ticks" a correction from the time server might have occurred during the interval and disturbed the tick count so that the result would be unreliable anyway.
When I wrote the question I meant "reliable within the capability of the system to determine the time". I should have included those words, or something like them, in the question.
Tom
November 17, 2012 at 10:02 pm
I'm happy that we're starting to get questions with the more modern Data Types. Datetime should have been retired when Date, Time and DateTime2 were made available.
David
January 7, 2013 at 5:04 am
Difficult one..
--
Dineshbabu
Desire to learn new things..
Viewing 8 posts - 31 through 37 (of 37 total)
You must be logged in to reply to this topic. Login to reply