June 29, 2015 at 9:12 am
Thanks Shiva. However I had to translate it twice to Czech. Firstly, what is "Which one of these doesn't match the others?", secondly, what "Who is the odd man out?". Then I was already sure with the question... 🙂
June 29, 2015 at 9:12 am
I got it wrong because figured out that NEWID() was the only one that would show different results if called more than once in the same row. Also because I read GETDATE instead of DATEADD :hehe:
SELECT CURRENT_TIMESTAMP,
DENSE_RANK() OVER(ORDER BY (SELECT NULL)),
NEWID(),
DATEADD(dd,1,GETDATE()),
ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),
CURRENT_TIMESTAMP,
DENSE_RANK() OVER(ORDER BY (SELECT NULL)),
NEWID(),
DATEADD(dd,1,GETDATE()),
ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM sys.all_columns
June 29, 2015 at 9:23 am
I think getting it right was all luck. Like many others have said, the question was very vague. Like Tom said, it does require thought on how they're similar and different, but I came come up with a few.
June 29, 2015 at 9:43 am
sknox (6/29/2015)
TomThomson (6/29/2015)
For example CHECKSUM(*) is nondeterministic, despite the fact that it will always return the same value whenever called unless the state of the database has changed between calls...That depends. Do you consider a non-persisted calculated column a change to the state of the database? Try this:
CREATE TABLE #t1 (ID INT, DT AS GETDATE());
INSERT [#t1] ([ID]) VALUES (1), (2), (3)
Then run this a few times:
SELECT ID, CHECKSUM(*) FROM #t1
I see your point. But I'm not claiming that CHECKSUM() is always deterministic with argument '*', only that it's deterministic when all the columns in the list, whether explicit or implied by '*', are themselves deterministic.
The definition could be cleaned up a bit to make sense: reference to a column which is non-persisted and computed by reference to a nondeterministic function should make a call of CHECKSUM which refers to that column indeterministic (for all I know it already does when the column is named in the argument list); and checksum(*) should be indeterministic when the table has such a column, but not otherwise. The fact that it could change to have such a column in the future is neither here nor there, it's a database state change so by definition it doesn't impact determinism (it would be a nuisance to code round, of course, if it's a column in an indexed view - but it can't be, as * can't be used in a schema bound object). If DT were a deterministic column then CHECKSUM(ID, DT) would be deterministic but a change in database state could make it nondeterministic. Of course such a change can't happen if the column is involved in a schema bound view definition, and the correct solution for * would be the same: don't allow the meaning of * to change if a view is dependent on it. And since * is not allowed in schema-bound views, why claim it makes CHECKSUM non-deterministic? Has anyone observed a case where CHECKSUM(*) changes its result but CHECKSUM(<list of every column in the table>) doesn't? I certainly haven't, in fact I can't imagine there being any such case, and I suspect that what has happened is that whoever wrote the BOL page that claims CHECKSUM(*) is indeterministic is labouring under the delusion that "indeterministic" means "forbidden in <list of contexts>", ie it's a statement based on the perverted view of indeterminism that I complained about.
Tom
June 29, 2015 at 9:52 am
Curious...how is NEWID() considered a deterministic function?
June 29, 2015 at 9:58 am
james.milner (6/29/2015)
Curious...how is NEWID() considered a deterministic function?
It is not a deterministic function. The answer to the question states that DATEADD is the only deterministic function and all of the others are non-deterministic.
_______________________________________________________________
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/
June 29, 2015 at 12:35 pm
WAG and a miss here. Had no clue what the intent of the question author was.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 29, 2015 at 12:39 pm
Aha, should have actually read it!
June 29, 2015 at 1:15 pm
"Wrong" but not wrong: CURRENT_TIMESTAMP takes no arguments, the others require them (or empty parentheses at least).
But I bit on this vague QotD anyway, so maybe I was wrong to do so... 😉
Rich
June 30, 2015 at 3:26 am
Yes, I also thought same
June 30, 2015 at 5:08 am
Explain the criteria you are using to differentiate the question. That is all...
July 1, 2015 at 12:29 am
More answers are right (like rmbchaber said)
The discussion however is fascinating.
July 1, 2015 at 5:47 am
The parameters criteria was my first guess as well. The OP was vague in his assumptions.
July 2, 2015 at 8:17 am
Thanks for your question.
July 6, 2015 at 5:33 am
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.
Viewing 15 posts - 16 through 30 (of 35 total)
You must be logged in to reply to this topic. Login to reply