The Interview

Thu Feb 12, 2004 by Brett Kaiser (x002548) in sql-server

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

Legacy Comments


Tara 2004-02-12	re: The Interview What's weird is that if you change: then floor (@l) to: then 2 or then 'Tara' You get 24.35.

Paul 2004-02-13	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....

Tara 2004-02-13	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.

James Curran 2004-02-13	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.

Tara 2004-02-13	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.

James Curran 2004-02-13	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)

James Curran 2004-02-13	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

Brett 2004-02-13	Thanks Man that is [b]GREAT[/b] Feedback. Thanks

ispaleny 2004-02-16	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).

SpiderMan 2004-02-17	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.