T-SQL small Question

Question

Post reply

T-SQL small Question

~sudhirchawla

SSC Eights!

Points: 808
More actions
November 20, 2004 at 5:22 am

#162817

In SQL editor, what will be the result of this query

Select 100/3

Will it be
A) 33.33
B) Error
C) 33.00
D) 34

Sudhir Chawla

Viewing 6 posts - 1 through 5 (of 5 total)

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

rajiv-170183 Ten Centuries Points: 1167 More actions · Answer 1

hey sudhir

i think it would be jus 33.b cos of integer division

am i right or wrong?

regards

Rajiv.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 2

Select 100/3

-----------

33

(1 row(s) affected)

and yes, it's because both operands are interpreted as integer, thus the result is also an integer. But why don't you try this yourself in QA? Should be only a matter of seconds. Actually I guess much faster than waiting here for an answer.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

~sudhirchawla SSC Eights! Points: 808 More actions · Answer 3

Guys,

I have already tried this, it will give 33.00, the reason is that if we use any value which is int compatible, SQL compiler will does an implicit conversion & then produces the output in int format only.

But if you put 100.00/3, it will give 33.33333 as result. The reason is it will treat this as numeric & division by integer = numeric result. This time the compiler will does an implicit conversion to numeric & then give the correct result.

My idea was to share this knowledge. Thank guys.

sudhir

Sudhir Chawla

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

Well, I'd say the 33 in the first example is also "correct". When you read up on data type precedences in BOL you'll find it there explained.

Also, for the lazycoder you can use

select 100/3. or select 100./3

to get the same result. Just put a dot in and SQL Server won't treat it as integer anymore.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

SQLBill SSC Guru Points: 51440 More actions · Answer 5

Sudhir,

I agree with Frank and disagree with you. The answer is NOT 33.00. If both numbers are INT as you show them, the answer will be INT. INT does not allow for decimals. The result must be a whole number so you can't have the .00. I also tried this in Query Analyzer.

-SQLBill