The coolest SQL Server function you never heard of
Ever heard of the SQL_VARIANT_PROPERTY function? I didn’t think so.
SQL Server developers very often make the mistake of making their NUMERIC fields too large. When faced with a choice of how to size the column, they’ll often think “make it way larger than I need to be safe”.
This works OK as long as you simply store and read these values, but if you ever have to perform math with these columns, particularly some form of division or multiplication, you may find your results mysteriously losing precision.
This is because SQL Server can only store a maximum of 38 digits per number… if the results of your mathematic expression may yield a number larger than that, SQL Server will be forced to downsize it and remove digits from the mantissa as a result.
For example, let’s say you are dividing two NUMERIC(30, 10) numbers as follows:
declare @x NUMERIC(30, 10) = 10.0 declare @y NUMERIC(30, 10) = 3.0 declare @result NUMERIC(38,10) set @result = @x / @y print @result
Your result, even though you were hoping for 10 digits of precision actually gives you 3.3333333300… only 8 digits.
Well, When you divide two NUMERICs with precision of 30, you can end up a result too large to store… SQL Server is forced to shrink the right side of your result to accommodate a maximum size of 38 for the result.
For division this is a relatively nasty determination. The nitty-gritty algorithm of how this works can be found here: http://msdn.microsoft.com/en-us/library/ms190476.aspx.
A very useful function for figuring out what your result will yield is the SQL_VARIANT_PROPERTY function. You use it as follows:
declare @x NUMERIC(30, 10) = 10.0 declare @y NUMERIC(30, 10) = 3.0 declare @result NUMERIC(38,10) set @result = @x / @y print @result select SQL_VARIANT_PROPERTY(@x / @y, 'BaseType') AS BaseType, SQL_VARIANT_PROPERTY(@x / @y, 'Precision') AS Precision, SQL_VARIANT_PROPERTY(@x / @y, 'Scale') AS Scale
And the results look like this:
The SQL_VARIANT_PROPERTY function can tell you details about the type derived from your expression. Here we can see that it reduced the scale to 8 which is why I’ve lost two digits of precision.
You’ll need to reduce your base types or cast them before doing the math to have enough room to get the desired precision in this case.
Documentation on SQL_VARIANT_PROPERTY here:
Details on Precision and Scale here: