0

My query:

(SELECT (...abbreviated...), r.CardInformation, SYSDATETIME(), NULL ,''
FROM {1} r
LEFT OUTER JOIN [{2}] p ON r.ProcessDate = p.ProcessDateTime AND r.ProcessType = p.ProcessType AND r.SalesAmount = p.SalesAmount AND r.TerminalID = p.TerminalID AND r.SlipNumber = p.SlipNumber
WHERE r.ProcessDate >= '{3}' AND r.ProcessDate < '{4}' AND r.ProcessType >= '1' AND r.ProcessType <= '3' AND r.ErrorType = '0' AND r.ErrorCode = ' ' AND r.ProcessResult <> '0' AND r.ProcessResult <> ' ' AND r.ReversalType = '0'

UNION

SELECT (...abbreviated...), r.CardInformation, SYSDATETIME(),NULL ,''
FROM {1} r
LEFT OUTER JOIN [{2}] p ON r.ProcessDate = p.ProcessDateTime AND r.ProcessType = p.ProcessType AND r.SalesAmount = p.SalesAmount AND r.TerminalID = p.TerminalID AND r.SlipNumber = p.SlipNumber
WHERE r.SalesFixDate = '{3}' AND r.ProcessDate < '{4}' AND r.ProcessType >= '1' AND r.ProcessType <= '3' AND r.ErrorType = '0' AND r.ErrorCode = ' ' AND r.ProcessResult <> '0' AND r.ProcessResult <> ' ' AND r.ReversalType = '0' )

I would like to know the execution order of the above statement. Specifically, I want to know what situations (if any) where the call to sysdatetime() in both select statements will be different. In one of our production servers, the above statement for some reason on a particular day/time caused a time gap of .003 seconds between the two calls of sysdatetime(). Is this expected behavior? As it is one query sent to the SQL Server, wouldn't the two calls to sysdatetime() produce the same results? Any thoughts on this is appreciated.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Andrew Winsinger
  • 1
  • The only way to answer this question is to view your actual execution plan. How SQL Server chooses to execute a query is up to its query engine, and could easily change on different servers due to different statistics etc. – Dale K Nov 09 '20 at 05:38
  • 1
    The link that Dale K shared answered my question. Thank you! Summarization of linked answer: The call to getdate() (I assume sysdatetime() has the same behavior) returns the same value in all rows regardless of how long the query takes to run. But there is no guarantee that different references in the SAME query will have the same value. – Andrew Winsinger Nov 09 '20 at 07:48

0 Answers0