T-SQL coding

Numeric Column Updates/Calculations

I recently had a developer come to me with a “problem”; she said that she issued an UPDATE statement to update few records but the values didn’t change. I found that the UPDATE did take place.  The issue was that the column was defined as DECIMAL(8,2), but her update statement was trying to change the value to a decimal with 3 places.  SQL Server automatically rounds values for you when working with numeric data that has mixed precisions; be careful of this when writing your code.  It also shows itself when you are multiplying or dividing.  Always make sure that column datatypes and variable datatypes include enough precision to account for any operations that you plan to use.

Here’s a quick example (I am using SQL Server 2014; earlier versions also exhibit this behavior). Run this example in your favorite “sandbox” database:

-- create a table to demonstrate
create table MySample (MyKey int not null,
     MyRate decimal(8,2) not null,
     MyRateInt int not null );
-- populate it with sample rows
insert into MySample values (1,1.02, 1.02),
(2,.058, .058);

-- check out the rates!
select MyKey, MyRate, MyRateInt,
MyRate * 1.555 as Mult,
MyRate / 1.555 as Divide
from MySample;

drop table MySample;

In this example I try to insert a value of .058 (three decimal places) into column MyRate for record #2. The first item to note is that this value is rounded upon insertion.  The SELECT shows that the value in the table is .06.  SQL Server doesn’t generate an error, it automatically rounds the value to something that will fit into the precision of the column (in this case 2 decimal places).

The second item to note is that when dealing with integers SQL Server truncates instead of rounding. See the SELECT results for column MyRateInt; you will see that in truncates the value for both records.

The third item to note is that when performing arithmetic SQL Server will calculate the precision based on the datatypes involved and on a series of rules that may or may not make sense to you (there is a link below that explains some of those rules). Note the precision of my results in the example above for multiplication and division.  When you mix decimal and integer types and arithmetic it gets even more strange…my advice is to make sure you use the maximum precision that you will ever need, and ALWAYS test out your arithmetic using values on both the low and high end of your expected calculations to ensure that SQL Server is not implicitly not truncating or rounding incorrectly for each situation.

Here is a link for some of the implicit precision determination that can occur:

https://msdn.microsoft.com/en-us/library/ms190476(sql.110).aspx

Advertisements

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