Who is the odd man out?

Question

Post reply

Who is the odd man out?

Shiva N

Hall of Fame

Points: 3125
More actions
June 28, 2015 at 8:49 am

#299523

Comments posted to this topic are about the item Who is the odd man out?
Thanks,
Shiva N
Database Consultant
June 29, 2015 at 3:20 am

This was removed by the editor as SPAM

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

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

Toreador SSChampion Points: 11358 More actions · Answer 1

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 More actions · Answer 2

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: 259085 More actions · Answer 3

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

free_mascot One Orange Chip Points: 27169 More actions · Answer 4

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: 259085 More actions · Answer 5

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: 11358 More actions · Answer 6

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: 259085 More actions · Answer 7

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: 5190 More actions · Answer 8

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

psingla Hall of Fame Points: 3840 More actions · Answer 9

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 More actions · Answer 10

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 More actions · Answer 11

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: 12388 More actions · Answer 12

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: 286573 More actions · Answer 13

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/