The coolest SQL Server function you never heard of

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:

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.