Who is the odd man out?

  • 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... 🙂

  • 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

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • 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.

  • 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

  • Curious...how is NEWID() considered a deterministic function?

  • 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/

  • 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

  • Aha, should have actually read it!

  • "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

  • Yes, I also thought same

  • Explain the criteria you are using to differentiate the question. That is all...

  • More answers are right (like rmbchaber said)

    The discussion however is fascinating.

  • The parameters criteria was my first guess as well. The OP was vague in his assumptions.

  • Thanks for your question.

  • 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 36 total)

You must be logged in to reply to this topic. Login to reply