Predict the outcome of the SQL statements

Question

Predict the outcome of the SQL statements

bazzkar

SSC Veteran

Points: 270
More actions
November 1, 2010 at 10:24 pm

#228700

Comments posted to this topic are about the item Predict the outcome of the SQL statements

Viewing 11 posts - 1 through 10 (of 10 total)

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

UMG Developer SSChampion Points: 13482 More actions · Answer 1

Nice question, thanks!

Though I think the explanation left a little to be desired. It isn't just that the decimal type was used, it was that the decimal type was used and no precision/scale was specified, so the number got rounded down to 1.0 when it was converted.

For example this results in the same thing as all of your float examples:

select Ceiling(convert(decimal(2, 1), 1.09))

Because with a scale of one specified it gets rounded to 1.1 before it goes to ceiling.

Toreador SSChampion Points: 11366 More actions · Answer 2

Knew the answer but had a brain fart and clicked the wrong option, oops!

UMG Developer is right about the explanation.

Mike Is Here Hall of Fame Points: 3348 More actions · Answer 3

Good question but I think a better explaination is:

the default of decimal without parameters is (18,0) thus making 1.09 -> 1 and then the ceiling of 1 is 1

John.Norcott SSC Journeyman Points: 99 More actions · Answer 4

Thank you Old Hand. I knew the ceiling function brought you up to the next integer, but I couldn't figure out why the decimal was any different than the float on this one! You're explanation was what I was missing. For some reason I thought the default precision was 2, not 0. I'll be sure to remember that the next time I use a decimal data type.

Toreador SSChampion Points: 11366 More actions · Answer 5

I hope one thing that everybody has learned from these questions is that you should never rely on default precision for anything - you will inevitably get it wrong sooner or later 🙂

TomThomson SSC Guru Points: 104773 More actions · Answer 6

Good question, but as some have already noted the explanation is a bit lacking.

Also, the question was made too easy by the absence of the All 2s option as an answer choice - anyone who knows what ceiling means can eliminate all answers but the correct one with knowing anything at all about decimal or its default precision.

Tom

Cliff Jones SSChampion Points: 10547 More actions · Answer 7

Tom.Thomson (11/2/2010)
Good question, but as some have already noted the explanation is a bit lacking.
Also, the question was made too easy by the absence of the All 2s option as an answer choice - anyone who knows what ceiling means can eliminate all answers but the correct one with knowing anything at all about decimal or its default precision.

Yes, I agree. The best wrong answer was missing.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

Thanks for the question.

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

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 9

Good question, although it was more about convert than about ceiling.

A link to the msdn page for convert and decimal:

http://msdn.microsoft.com/en-us/library/ms187928(SQL.90).aspx (convert)

http://msdn.microsoft.com/en-us/library/ms187746.aspx (decimal/numeric)

The last page describes the gotcha of this question in the explanation of the scale.

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

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 10

Hardy21

SSCrazy Eights

Points: 9708

November 9, 2010 at 1:40 am

#1248061

Nice question. Thanks

Thanks