The Interview

Posts: 83
Comments: 600
Trackbacks: 40

<< Come on Weekend! | Home | Dynamic Predicates? >>

I've decided to start a list of questions for canidates (or so I can remeber when I gotta go)...ones that I've run across and was confused by (yeah, yeah, there's not enough drive space available...so I'll keep it breif)

04/15/2004

Q:What do you do with an index intersection?

A: Look both ways, before you cross join.

02/12/2004

1. Why do the 2 statements below return different results?

declare @l decimal(38,2)
select @l = 24.35

if @l - convert(int,@l) = 0
    select floor (@l)
else
    select @l

select case when @l - convert(int, @l) = 0 then floor (@l)
                                                else @l
       end

posted on Thursday, February 12, 2004 1:56 PM Print

Comments

# re: The Interview: Tara; 2/12/2004 5:15 PM; What's weird is that if you change:

then floor (@l)

to:

then 2

or

then 'Tara'

You get 24.35.

# re: The Interview: Paul; 2/13/2004 5:19 AM; 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: Tara; 2/13/2004 12:40 PM; 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: James Curran; 2/13/2004 12:46 PM; 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: Tara; 2/13/2004 12:51 PM; 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: James Curran; 2/13/2004 1:12 PM; 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: James Curran; 2/13/2004 1:26 PM; 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: Brett; 2/13/2004 2:29 PM; Man that is [b]GREAT[/b] Feedback.

Thanks

# re: The Interview: ispaleny; 2/16/2004 8:30 AM; 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: SpiderMan; 2/17/2004 6:04 AM; 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.

Comments have been closed on this topic.

Article Categories

Archives

Post Categories

Image Galleries

Misc

SQL Server

x002548's Blog