x002548's Blog

# re: The Interview

What's weird is that if you change:

then floor (@l)

to:

then 2

or

then 'Tara'

You get 24.35.

# re: The Interview

In the CASE statement the SQL interpreter is making an implicit conversion of the first @l into an integer, and therefore the result is true...

Why does it work like that?

Ask MS....

# re: The Interview

Yes, it does an implicit conversion. But why does the answer depend on what is in the THEN part like my comment mentions. If you change floor(@l) to 'Tara, you'll get 24.35 instead of 24.

when @l - convert(int, @l) = 0 should evaluate to the same answer regardless of what is in the THEN part.

# re: The Interview

You can simplify it to just:
select case when 1=0 then floor(@l)
else @l end

as the value in the case doesn't matter, as long as it's false.

The case statement, as a whole, will always be one data type (remember, this will ususally be a calculated column in a table query, and you can't have that column change datatype row-to-row). So, SQL will look at all the data types of possible return values, and choose the highest precedent as the data type of the column.

So, in Tara's example, the choices were Decimal(38,2) & int and Dec(38,2) & varchar(4). In both cases, SQLS choose to make the column a Dec(38,2). (Try "select case when 1=1 then 'Tara' else 2 end" and you'll get a data conversion error.

So, why does this cause the proble we're selling? As far as I can tell, the data type of the return value of floor() is Dec(38,0), and that type is winning the precedence war. If we change it to:
select case when 1=0 then Convert(dec(38,2),floor(@l))
else @l end

then we get the correct value regardless of which way the condition is set.

# re: The Interview

Ah, thanks! That's what I had figured by reading CASE in BOL, but I didn't know how (actually didn't spend too much time on it) to prove it.

# re: The Interview

Ok, building the ridiculoius statement:
select (case when 1=0 then floor(@l) else @l end) as expr,
SQL_VARIANT_PROPERTY ( (case when 1=0 then floor(@l) else @l end) , 'BaseType' ) as 'BaseType',
SQL_VARIANT_PROPERTY ( (case when 1=0 then floor(@l) else @l end) , 'Precision' ) as 'Precision',
SQL_VARIANT_PROPERTY ( (case when 1=0 then floor(@l) else @l end) , 'Scale' ) as 'Scale'


we get the results:
expr BaseType precision Scale
24 decimal 38 0

which prove my guess about the type of the result of the case expression. What's very interesting is if you do a


select floor(24.35) as expr,
SQL_VARIANT_PROPERTY ( floor(24.35) , 'BaseType' ) as 'BaseType',
SQL_VARIANT_PROPERTY ( floor(24.35) , 'Precision' ) as 'Precision',
SQL_VARIANT_PROPERTY ( floor(24.35) , 'Scale' ) as 'Scale'

you get
expr BaseType precision Scale
24 numeric 4 0

Naturally,

select @l as expr,
SQL_VARIANT_PROPERTY (@l, 'BaseType' ) as 'BaseType',
SQL_VARIANT_PROPERTY ( @l , 'Precision' ) as 'Precision',
SQL_VARIANT_PROPERTY ( @l , 'Scale' ) as 'Scale'

give us
expr BaseType precision Scale
24 decimal 38 0

If in Data Type precedence decimal beats numeric (as per the precedence table in the BOL), the higher precision wins (reasonable), but the LOWER scale win, which while it may be counter-intutitive, is the correct way of do thing. If we had the number 1.5 & 1.234 and had to display them on the same scale, are choices are "1.5" & "1.2" or "1.500" & "1.234". The first is technically correct, while the second pair is implying information we don't have (e.g., the first value could have been previously rounded down from 1.543)

# re: The Interview

Ooops.... Copy'n'pasting from the comment edit window is easier than do so from the Query Analyser. But then you have to edit it so it agrees with what QA says.... All of which means, the results of the last SELECT should have been:

expr BaseType precision Scale
24.35 decimal 38 2

# Thanks

Man that is [b]GREAT[/b] Feedback.

Thanks

# re: The Interview

Brett,
in BOL

mk:@MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\tsqlref.chm::/ts_fa-fz_6lo2.htm

there is a wrong info about FLOOR(numeric_expression)

"Returns the same type as numeric_expression."

Your number starts with numeric(38,2) and it is converted by FLOOR() function to numeric(38,0).

# re: The Interview

The floor function returns the same type as the numeric_expression, but for expressions of type numeric or decimal, the results have a precision equal to that of the expression and a scale of 0.