Hi everyone,
I have a SELECT statement that uses GETDATE() for two different column values. I'm wondering if by the nature of things those two separate function calls in the same SELECT will return identical values every time?
views:
17answers:
3
+4
A:
Yes, they will return the same date and time, to the millisecond.
SELECT GETDATE(), GETDATE()
Returns
2010-10-29 15:34:06.353 2010-10-29 15:34:06.353
I have tested this with 4000, GETDATE() and they all return the same.
Dustin Laine
2010-10-29 22:34:29
+2
A:
Yes, you will get the same value in both columns on every row of the query. While GETDATE() is non-deterministic, it will only get one value for all occurrences in the query.
bobs
2010-10-29 22:38:40
+1 for the "non-deterministic" addition!
Dustin Laine
2010-10-29 22:55:08
@Dustin Laine, @bobs: "non-deterministic" has no bearing on the *per query* evaluation behaviour.
gbn
2010-10-30 09:53:21
@gbn, Thanks for the comment, it made me dig deeper. There is a great SO post about this that you actually answered. http://stackoverflow.com/questions/3620105/sql-server-intrigued-by-getdate. Worth a look on this topic.
Dustin Laine
2010-10-30 17:36:11
A:
Most system functions are evaluated per query, not per row, except those that are by definition statistically unique such as NEWID(). This includes things like RAND() (which is not unique, only psuedo random) unless seeded randomly row by row with NEWD().
Determinism is not related to this evaluation because it means "the output is the same for any given input independent of, say, language or DMY/MDY)
This "once per query" evaluation makes sense of course, especially for GETDATE().
If I do a 10k row update, I want every row to have the same GETDATE() value. The entire update could easily take > 3.33 milliseconds and I don't want different values over my 10k rows.
gbn
2010-10-30 09:52:06