Execution Order of UNION statement with call to sysdatetime()

Question

Our 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' )

We would like to know the execution order of the above statement. Specifically, we 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 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.

Answer 1

Hi @Andrew Winsinger ,

The standard SQL parsing sequence is:
(1). FROM clause, to assemble data from different data sources
(2). WHERE clause, filter records based on specified conditions
(3). GROUP BY clause, divide the data into multiple groups
(4). Use aggregate functions for calculation
(5). Use HAVING clause to filter groups
(6). Calculate all expressions
(7). Use ORDER BY to sort the result set

Is this the time difference when you execute the two select statements separately?
The same statement is executed many times, but the time is different. I often encounter this situation. I am not sure what caused this, but this is a normal phenomenon. Maybe it is related to memory and cpu.

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October--https://learn.microsoft.com/en-us/answers/questions/142427/users-always-get-connection-timeout-problem-when-u.html

Answer 2

If you execute manually a query like this:

select SYSDATETIME()
union
select SYSDATETIME()
union
select SYSDATETIME()
union
. . . ,

which contains enough lines (a thousand or more, after some copy-paste operations in Management Studio), you will achieve a result that contains more than one row. It is probably because SYSDATETIME is a “non-deterministic” function, which is re-evaluated. Occasionally, two evaluations will return different values (even if you simplify the example).

Perhaps you can store the value of SYSDATETIME to local SQL variable and use it in SELECT. Or you can return this value separately using a distinct SELECT in the same query (batch).

Answer 3

That is normal and expected behavior. If you need them to match, you must save the value to a variable and SELECT that variable.