Toreador (12/8/2009)
sknox (12/4/2009)
Steve Eckhart (12/4/2009)
select cast(1.67574 as decimal(38,10)) = 1.675400000 which has precision 6 and scale 10, right?and
select cast(10000 as decimal(38,10)) = 10000.0000000000 which has precision 1 and scale 10, right?
wrong, and wrong. Because you're explicitly casting to decimal(38,10), both of your results have precision of 38 and scale of 10 even though they don't apparently require it. SQL server will not narrow the precision and scale of an explicitly cast decimal result.
I'm struggling here.
Surely that's exactly what SQLServer is doing?
No. The equation in the QotD was:
CAST( cast(1.67574 as decimal(38,10)) / cast(10000 as decimal(38,10)) AS DECIMAL(38,10) )
Procedurally, this is what SQL Server does:
Step 1 is to cast 1.67574 as decimal(38, 10). This explicit cast is not narrowed, which is one reason why the calculation in (3) results in a result that has to be narrowed.
Step 2 is to cast 10000 as decimal(38, 10). This explicit cast is not narrowed, which is the other reason why the calculation in (3) results in a result that has to be narrowed.
Step 3 is to perform the division. This results in an answer whose precision and scale are beyond the ranges of the decimal data type. So the precision and scale are narrowed as discussed in this topic. Note that technically, this result is not explicitly cast.
Step 4 is to cast the final result (i.e, after the internal narrowing) as decimal(38,10). This explicit cast is also not narrowed, which is why we see the trailing 0s in the result.
An explicit cast that falls within the ranges of the decimal data type will not be narrowed. An explicit cast that falls outside the ranges of the decimal data type will result in an error. A calculation which results in precision and scale outside the ranges of the decimal data type will be narrowed to fit within those ranges as discussed.
I agree that the explanation on the QotD didn't explain it. That's why I did the research I did.