Sean Lange (7/8/2015)
Is it not true that there are no non-deterministic functions. Simply those that fully specify their inputs and those that don't?
Or put another way, the definition of non-determinism is that it uses a global variable or external source of data.
Well, it's at least as accurate as the definition given in BOL.
If you want to be really accurate, the nearest thing I can think of as an effective (as opposed to nominal, but ineffective) SQL Server definition of a deterministic function call is that it's one one whose arguments (that is, the arguments provided between the opening and closing parentheses (if any) of the function call) involve neither the abbreviation "*" when it represents all columns in a base or derived table (as opposed to when it represents a row - COUNT(*) is determistic unless influenced by an over clause) nor anything which itself is not deterministic, and which when called depends only on (a) the arguments (as above) (if any) of the function call and (b) the current state of the database (as far as schemata, not connections, are concerned) and (c) the current states of SQL variables in the context of the function call and (d) the query (excluding any over clause) in which the call occurs and is independent of the state of the RAND sequence generator in the current session, independent of the state of the GUID generators in the system, and is independent of the date and is independent of the time.
Why group by introduces no non-determinism but partition by introduces determinism, and order by introduces deterministm or doesn't according to whether it is in an over clause or not is altogether unclear - there is no attempt to explain this in the documentation, which simply states that aggregates are deterministic unless used with an over clause.
I think that aron's statement is at least as clear as the crazy definition I've written above, and probably more useful; it's infinitely better than the definition
Deterministic functions always return the same result any time they are called with a specific set of input values and given the same state of the database. Nondeterministic functions may return different results each time they are called with a specific set of input values even if the database state that they access remains the same.
provided in BOL, because it provides a far better chance of deducing from it whether a particular function is deterministic that does the BOL definition; but "far better" doesn't, unfortunately, mean "perfect" and it's still necessary to learn the list of what is or isn't deterministic by rote or, if one prefers not to learn like a parrot, to look up whether the function is or isn't definitive when one needs to know.