Home > SQL > The coolest SQL Server function you never heard of

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.

What happened?

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:

sql_variant_property

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:

http://msdn.microsoft.com/en-us/library/ms178550.aspx

Details on Precision and Scale here:

http://msdn.microsoft.com/en-us/library/ms190476.aspx

About these ads
  1. May 8, 2013 at 4:44 pm

    Reblogged this on Fortigent.

  2. June 3, 2013 at 7:16 pm

    Your code is missing the –JKM tag . . .

  3. June 3, 2013 at 8:03 pm

    Names were removed to protect the innocent… or the guilty.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 44 other followers

%d bloggers like this: