July 6, 2015 at 2:36 pm
I agree that's also why I picked CURRENT_TIMESTAMP.
July 8, 2015 at 5:23 am
I got wrong answer. I assume that all these can be converted int only but newid will throw error if we convert into integer.
Malleswarareddy
I.T.Analyst
MCITP(70-451)
July 8, 2015 at 7:13 am
aron.vandermeyden (7/6/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.
Huh???
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 8, 2015 at 4:09 pm
Sean Lange (7/8/2015)
aron.vandermeyden (7/6/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.
Huh???
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
BOL
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.
Tom
October 23, 2015 at 6:48 am
I voted, and still vote, for CURRENT_TIMESTAMP as there are no parantheses.
March 16, 2016 at 1:44 am
Yep some more context please.
I went for NEWID as the others are available in AZURE but NEWID isn't.
Viewing 6 posts - 31 through 35 (of 35 total)
You must be logged in to reply to this topic. Login to reply