Who is the odd man out?

  • Shiva N

    Hall of Fame

    Points: 3125

    Comments posted to this topic are about the item Who is the odd man out?

    Thanks,
    Shiva N
    Database Consultant

  • Toreador

    SSChampion

    Points: 11253

    The question is too vague. I'm sure there are reasons for choosing most of them.

    For instance, I went for CURRENT_TIMESTAMP because it does not require (and indeed cannot have) parentheses. ie

    select CURRENT_TIMESTAMP

    and not

    select CURRENT_TIMESTAMP()

    All the rest require arguments except for NEWID which requires empty parentheses,

    select NEWID()

  • Heals

    SSCrazy

    Points: 2234

    I agree - there needs to be some context (I got it right but only by fluke as I just guessed). Depending on what the author was thinking - any of them could be the odd one out...

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    Interesting question, thanks.

    A bit more context could have been useful, as others have pointed out.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • This was removed by the editor as SPAM

  • free_mascot

    One Orange Chip

    Points: 27168

    Thanks Shiva.

    It would be good if more context have been used. As ROW_NUMBER and DENSE_RANK is "Ranking Functions", DATEADD and CURRENT_TIMESTAMP is "Date and time function". Now NEWID is sytem Function hence consider as odd man out.

    ---------------------------------------------------
    "Thare are only 10 types of people in the world:
    Those who understand binary, and those who don't."

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    free_mascot (6/29/2015)


    Thanks Shiva.

    It would be good if more context have been used. As ROW_NUMBER and DENSE_RANK is "Ranking Functions", DATEADD and CURRENT_TIMESTAMP is "Date and time function". Now NEWID is sytem Function hence consider as odd man out.

    I'm not sure what you mean with "system function"?

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Toreador

    SSChampion

    Points: 11253

    Koen Verbeeck (6/29/2015)

    I'm not sure what you mean with "system function"?

    https://msdn.microsoft.com/en-us/library/ms187786.aspx

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    Toreador (6/29/2015)


    Koen Verbeeck (6/29/2015)

    I'm not sure what you mean with "system function"?

    https://msdn.microsoft.com/en-us/library/ms187786.aspx

    Thanks.

    They have quite a broad definition:

    The following system functions perform operations on and return information about values, objects, and settings in SQL Server.

    Even ISNULL is a "system" function.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Michael Riemer

    SSCertifiable

    Points: 5096

    Very vague question - and as others say, multiple ways to consider something the odd one out.

  • psingla

    Hall of Fame

    Points: 3840

    Heals (6/29/2015)


    I agree - there needs to be some context (I got it right but only by fluke as I just guessed). Depending on what the author was thinking - any of them could be the odd one out...

    +1

    Anyways thanks for the question!

    Pramod
    SQL Server DBA | MCSE SQL Server 2012/2014

    in.linkedin.com/in/pramodsingla/
    http://pramodsingla.wordpress.com/

  • Xavon

    SSCrazy

    Points: 2280

    Agreed on the vagueness. I got it right for the wrong reason; I thought "the other four are for row identification (in some way, shape, or form) while DATEADD is not".

  • TomThomson

    SSC Guru

    Points: 104773

    It is indeed a rather vague question. That doesn't mean it's a bad question, though - it encourages us to think and to use our imaginations.

    I got the right answer by sheer luck because I picked the only one of the listed functions whose mandatory parameter list (excluding parameters shifted to an OVER clause) couldn't be counted on the thumbs of one hand, since I hadn't a clue what sensible crtiteria (if any) could be used to select the odd one out - and after reading the explanation I still can't (because using the definition of deterministic won't work, one has to learn, parrot-fashion, the list).

    Indeed, the whole deterministic/nondeterministic thing seems to be something of an awful kludge whose purpose rather unclear. 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 - i.e. it fully satisfies the definition given in the BoL page for a deterministic function, but it counts as indeterministic for some unspecified reason, perhaps because someone thinks * is a bad thing, or perhaps because having a column containing CHECKSUM(*) in an indexed view would require some (fairly trivial, actually) code to keep the view corect when columns are added to and/or removed from the underlying tables. I suspect the purpose has by now been perverted to be to avoid some rather boring coding problems in implementation, although it probably started out as a genuine attempt to ban nondeterminism in contexts where it would be silly, and that this perversion is why it has become a kludge with no relation at all with its own definition or with the normal use of the word deterministic.

    Tom

  • sknox

    SSChampion

    Points: 12284

    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

  • Sean Lange

    SSC Guru

    Points: 286531

    I too was bitten by the vague bug. I got the right answer but that was because it was the only non-ANSI function. There are just too many ways this question could be interpreted.

    _______________________________________________________________

    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/

Viewing 15 posts - 1 through 15 (of 36 total)

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