A blog about SQL Server, SSIS, C# and whatever else I happen to be dealing with in my professional life.

Find ramblings

Wednesday, December 19, 2007

SQL Server and datatype conversions

Transact SQL (TSQL) Pop Quiz

@sufficientStorage decimal(18, 2)
, @insufficient decimal(18, 0)

@sufficientStorage = 45.77

@insufficient = @sufficientStorage

@sufficientStorage AS sufficientStorage
, @insufficient AS insufficient

Does SQL Server say

A) Cannot explicitly convert from decimal(18, 2) to decimal(18, 0) due to a loss in precision

B) Implicitly round the value for you

C) Implicitly truncate the value for you

D) I have no idea what you're talking about nor do I care

E) Arithmetic overflow error converting numeric to data type numeric

Much to my surprise the answer ended up being B. I haven't checked BOL (books online) yet but this seemed odd. Most languages that I've been associated with will happily upsize values for you but you have to explicitly tell them "trust me, I know what I'm doing when I say I can squeeze this watermelon into a mason jar." Oh well, back to deciphering the code but that one was unexpected. I'll have to check BOL now because now I want to know what rounding method they use (yes, there is more than one way to round numbers, there is even an rounding standard to be used with any data going to the IRS if I recall correctly)

And yes, BOL has it all

"When SET NUMERIC_ROUNDABORT is ON, an error is generated after a loss of precision occurs in an expression. When OFF, losses of precision do not generate error messages and the result is rounded to the precision of the column or variable storing the result."

No comments: