Within a single query, all current time functions like sysutcdatetime() return a single point in time that is re-used throughout the query for each instance (e.g. of sysutcdatetime()) no matter how complex (afaik).
However NewId() and Rand() do not follow this behavior and diverge from each oth...
I think this is a very interesting question. Could be better formulated, but I'd like to see a good answer explaining the difference in behaviour between the various functions.
@ypercubeᵀᴹ Added explanation in comment as per duplicate.
See explanation in duplicate question: The GETDATE and SYSDATETIME functions are indeed non-deterministic, but they are treated as runtime constants for a particular query. Broadly, this means the function's value is cached when query execution starts, and the result re-used for all references within the query. and This behaviour is by design, ... for the NEWID function. — hot2use34 secs ago
@ypercubeᵀᴹ Covers part of it, but would be nice to have an answer that explains the differences between the three functions named in this question. All three functions behave differently in different contexts
Scratch that.
Covers most of it; presumably rand() works the same at the date/time functions
We have a few similar answers in the same area. That's the one with the quotation from Connect (RIP) that I think comes closest to the idea of official documentation. I left the comment rather than closing as a dupe because it was already closed, so it makes sense to see how people vote, and how the OP responds.
Maybe someone else could write a better answer, but if I answered it, I would use the same supporting links and much of the same phrasing.
Oh I see it has been reopened and closed as a dupe since I last visited the page.
@MaxVernon It's not great, I just padded the comment a bit because I thought it should be in an answer, whether it was added to yours or as another one. Definitely not a comment-answer
@Zane no. Is it reproducible? I think that would make an interesting question on the main site.
There are several limitations to the DROP_EXISTING setting mentioned in the docs, but they give different error messages (can't convert a clustered index to nonclustered, can't change the constraint enforced by a unique index, etc).