Problem
Please compare the SQL Server round function to banker’s rounding in T-SQL for converting decimal values to integer values. I seek a framework for assessing how closely banker’s rounding results versus SQL Server Round function results match the underlying decimal values. Please provide a couple of empirical comparisons with the framework to indicate which set of rounded values are closer to the underlying decimals and by how much.
Solution
T-SQL offers its built-in round function (along with other built-in functions) for converting a decimal value with places to the right of the decimal point to an integer value. The round function implements a normal style of rounding that rounds up whenever the value to the right of the decimal point is .5 or greater; the function does not round up when the value to the right of the decimal point is not .5 or greater.
The learn.microsoft.com site defines banker’s rounding as a rounding technique for removing ambiguity when the unrounded value is midway between two possible rounded values, such as 1.5 or 2.5. For example, 1.5 is midway between the integer values of 1 and 2. Likewise, 2.5 is midway between 2 and 3. Actually, both normal rounding and banker’s rounding remove the ambiguity, but the two rounding techniques resolve the ambiguity differently. Banker’s rounding uses two different rules when the value to the right of the decimal point is precisely .5:
- When the integer part of a decimal value is odd, and the digit to the right of the decimal point is 5, then banker’s rounding rounds up.
- When the integer part of a decimal value is even, and the digit to the right of the decimal point is 5, then banker’s rounding does not round up.
In the case of our sample values of 1.5 and 2.5,
- Normal rounding rounds 1.5 to 2. Similarly, banker’s rounding rounds 1.5 to 2.
- Normal rounding rounds 2.5 to 3. On the other hand, banker’s rounding rounds 2.5 to 2.
As you can see, normal rounding rounds up all the time, but banker’s rounding only rounds up when the integer value is odd. While this procedural difference is slight in that it only applies when the value after the decimal point value is precisely .5, the difference in outcomes may be substantial in some cases, such as when dealing with a large dataset.
This tip provides the T-SQL code for normal rounding and banker’s rounding. You will also learn how to assess the difference in rounded values for two datasets. One dataset is a set of uniformly distributed random decimal values, and the other dataset is a set of normally distributed random decimal values. You can learn more about these two distribution types with this prior tip that targets SQL Server professionals.
A User-defined T-SQL Function for Banker’s Rounding
MSSQLTips.com previously published a tip with a user-defined function to implement banker’s rounding, which included the code for the user-defined function as well as commentary on the function’s code and examples using it. The code for the user-defined function appears below for your convenience because it is so central to the current tip.
The script below creates the dbo.bankersround function in the DataScience database, but you can use any other database. The function accepts the @number parameter in a decimal(19,4) format and returns a rounded value in an int format.
use DataScience
drop function if exists dbo.bankersround
go
create function dbo.bankersround (@number decimal(19, 4))
returns int
as
begin
declare @integerpart int, @fractionalpart decimal(19, 4), @bankersround int
set @integerpart = convert(int, @number)
set @fractionalpart = @number - @integerpart
select @bankersround = case
when @integerpart % 2 = 0 and @fractionalpart = .5000 then @integerpart
when @fractionalpart < .5000 then @integerpart
else @integerpart + 1
end
return @bankersround
end
A Framework for Comparing Rounding of Uniformly Distributed Random Values
The framework for comparing the output from the built-in SQL Server round function to the user-defined bankersround function has five main steps.
Steps for Framework
- Step 1: Create a temporary table for storing uniformly distributed random decimal values.
- Step 2: Populate the temporary table with random decimal values.
- Step 3: Run a SELECT statement to round each uniformly distributed random decimal value with both the SQL Server round function and the bankersround function. Optionally display the original decimal values and the two rounded sets of values for a visual inspection in the operation of the two rounding methods.
- Step 4: Compute sums for the original values and the two rounded sets of values. Then, compare the sums for each rounded set of values to the sum of the original values.
- Step 5: Iterate through steps 1 through 4 as many times as desired to verify stable comparisons.
T-SQL Code for Implementing the Framework
The following code sample commences with a use statement that designates the DataScience database as the default for the script.
The first step in the framework invokes a conditional drop table statement to remove any prior version of the #rand_uniform_decimals table. Next, a create table statement creates a fresh version of the #rand_uniform_decimals table. The table has one column named urand_decimal with a decimal (2,1) datatype.
The second step in the framework uses a while loop to generate 1,000 uniformly distributed random decimal values in the range of 0.0 through 8.9. The first declaration statement in the loop generates a uniformly distributed random int value in the range of 0 through 90. The second declaration statement in the loop transforms the int value series to a decimal value series with a range of 0.0 through 8.9.
The third step optionally displays the original random values with a select statement, along with the SQL Server round function values for the random values and the bankersround function values for the original random values. You do not strictly need this select statement to compare the two round functions at an aggregate level. However, the results set from the select statement are convenient for illustrating the differences in the outputs from the two round functions.
The fourth step implements, with three nested select statements, the aggregate comparisons of the SQL Server round function values to the original random values, as well as the bankersround function values to the original random values.
- The innermost subquery, named for_urand_decimal_rounding_sums, contains the code in the third step.
- The middle subquery, named for_comps, computes a separate sum for the original random numbers, the SQL Server round function values, and the bankersround function values. This subquery references the for_urand_decimal_rounding_sums subquery in its from clause.
- The outermost subquery references the for_comps subquery in its from clause.
- The first three columns of the outermost subquery display the aggregate sum values for the original random values, the SQL Server round function values, and the bankersround function values.
- The fourth column displays the sum for the original random values less the sum for the SQL Server round function values.
- The fifth column displays the sum for the original random values less the sum for the bankersround function values.
The fifth step uses the SQL Server Management Studio (SSMS) go statement to repeatedly execute the batch in the following code sample five times. You can comment out the go statement if you want to run the sample code once, or you can change the iterator value following go if you want to run the batch in the code sample a different number of times than five. A prior tip in MSSQLTips.com describes and demonstrates how to use the iterator values with the SSMS go statement.
use DataScience
-- step 1
-- create a table for storing uniformly distributed random decimal values
-- from 0.0, 0.1, 0.2...8.7, 8.8, 8.9
drop table if exists #rand_uniform_decimals
create table #rand_uniform_decimals
(
urand_decimal decimal(2,1)
)
-- step 2
declare @maxnumbers int = 1000, @loop_id int = 1
-- generate a set of uniform random integers from 0 (inclusive) to 90 (exclusive)
-- a set has @maxnumbers members
while @loop_id <= @maxnumbers
begin
declare @randomInt INT = floor(rand() * 90);
declare @formattedNumber DECIMAL(2, 1) = @randomInt / 10.0;
-- return random decimal values
insert into #rand_uniform_decimals
select @formattedNumber
set @loop_id = @loop_id +1
end
-- step 3
-- original, SS rounded, and banker's rounded decimal values
--/*
select
urand_decimal
,round(urand_decimal,0) SS_urand_decimal_nearest_int
,DataScience.dbo.bankersround(urand_decimal) BR__urand_decimal_nearest_int
from #rand_uniform_decimals
--*/
-- step 4
-- sums and comps for original, SS rounded, and banker's rounded decimal values
select
[sum of original decimal values]
,[sum of SS_urand_decimal_nearest_int]
,[sum of BR__urand_decimal_nearest_int]
,[sum of original decimal values] - [sum of SS_urand_decimal_nearest_int] [SS round comparison]
,[sum of original decimal values] - [sum of BR__urand_decimal_nearest_int] [BR comparison]
from
(
-- sums for original, SS rounded, and Banker's rounded decimal values
select
sum(urand_decimal) [sum of original decimal values]
,sum(SS_urand_decimal_nearest_int) [sum of SS_urand_decimal_nearest_int]
,sum(BR__urand_decimal_nearest_int) [sum of BR__urand_decimal_nearest_int]
from
(
-- original, SS rounded, and banker's rounded decimal values
select
urand_decimal urand_decimal
,round(urand_decimal,0) SS_urand_decimal_nearest_int
,DataScience.dbo.bankersround(urand_decimal) BR__urand_decimal_nearest_int
from #rand_uniform_decimals
) for_urand_decimal_rounding_sums
) for_comps
-- step 5
go 5
Reviewing Selected Results from the T-SQL Code for Implementing the Framework
This subsection examines selected results from the preceding script to meet two objectives:
- Objective 1: Reveal how and when rounding with the built-in SQL Server round function returns a different result than the bankersround user-defined function. This subsection pursues this objective at the level of individual random values from each set of 1,000 original random values analyzed for successive re-runs of the code within the script.
- Objective 2: Reveal at an aggregate level how close the sum of the SQL Server round function values and the sum of the bankersround function values are to the sum of the original random decimal values. The closer the sums are, the less distortion due to rounding.
The following screenshot shows the first 20 rows from step 3 in the framework. This step returns a list of 1,000 rows for three columns with the original random values and the two corresponding sets of rounded values. For color coding purposes, the rows are copied to an Excel worksheet tab named urand_results_1. This name signifies the values are for the first of multiple sets of uniformly distributed values. Within the context of this tip, there are five multiple sets.
- The original random values appear in column A.
- The SQL Server round function values appear in column B.
- The bankersround function values appear in column C.
The first point to note is that for 18 of the 20 rows, columns A, B, and C contain identical values. Two of the first 20 rows contain colored numerical values; the colors are for rows that do not contain identical values.
- The colored rows are 10 and 17.
- The values in row 10 appear in a green-colored font to highlight that banker’s rounding rules cause the original value to round up. Because the SQL Server round function always rounds up when the digit to the right of the decimal point is 5, the values in columns B and C for row 10 match perfectly.
- The values in row 17 appear in a red-colored font to highlight that banker’s rounding rules do not cause the original value to round up. Consequently, the value in column C is less than the corresponding value in column B.
The following screenshot shows the bottom 20 rows from step 3 in the framework. For this subset of rows from the first set of 1,000 comparisons, there are two rows with colored values. In both cases, the colored rows display values in red text. This is because the banker’s rounding rule does not round up when the digit after the decimal point is 5, but the integer value of the decimal value being rounded is an even number.
As these two preceding screenshots illustrate, the comparative results can differ in how often rows are rounded up with banker’s rounding rules relative to the SQL Server round function. This is because the banker’s rounding rule consistently:
- Fails to round up when the digit after the decimal point is 5, and the integer before the decimal point is even.
- Rounds up when the digit after the decimal point is 5, and the integer before the decimal point is odd.
The next screenshot compares the sum of the rounded values to the original values for each set of 1,000 random values generated. There are five rows of values in the screenshot – one row for each set of 1,000 random values generated for this tip. Columns E and F reflect the difference between the sum of the original random values less the sum of rounded values.
- Column E shows the aggregate difference between SQL Server round function values and the original random values.
- Column F shows the aggregate difference between bankersround function values and the original random values.
For all five comparisons, the sum of the bankersround function values is closer to the sum of the original random values than the sum of the SQL Server round function values. Across these five sets of 1,000 rows each, the banker’s rounding rules return values that more closely match the underlying values than the SQL Server round function. Both functions perform rounding, but the bankersround function introduces less distortion of the underlying values. This is because the SQL Server round function rounds up all the time when the digit after the decimal point is 5, but the bankersround function only rounds up half of the time.
Rounding Comparisons for Normally Distributed Values
This section compares the SQL Server round function to the bankersround function for normally distributed random values instead of uniformly distributed values as in the preceding section. The purpose of this comparison is to assess if the differences reported for uniformly distributed random values in the preceding section also apply to values for another commonly encountered type of data distribution. The second round of comparisons covered in this section will reinforce your understanding of which rounding technique to use in future data analysis and production applications.
T-SQL Code for Implementing the Framework for Normally Distributed Values
The following script shows code to populate a table with 1,000 normally distributed values. The code listing includes comment markers to indicate the commencement of each framework step. The process for designating a set of normally distributed values in this section critically includes setting a mean of 4.5 and a standard deviation of 1. The mean value is the center point of the distribution for values from 0.0 through 8.9. The standard deviation of 1 is a common value for the spread of values in a normal distribution. Next, the code calculates rounded values by the SQL Server round function and the bankersround function for each normally distributed value. The set of normally distributed values, along with the rounded values, is optionally displayed via a select statement. The next-to-the-last step computes the sum of normally distributed values as well as the two sets of rounded values. Finally, the last step references the go keyword with an iterator that specifies the code in the batch should be run five times.
It may be worth mentioning that the process for generating normally distributed random values is not the same as generating uniformly distributed random values. You may have already observed that you specify mean and standard deviation values to designate normal distribution of values, but you specify starting and ending values for a uniform distribution. Also, the code for generating a normal distribution of values generates two random decimal values on each pass through a while loop, but the code for generating a uniform distribution of decimal values creates one value for each pass through a while loop. This return of two normally distributed values per loop pass is because of the Box-Muller algorithm for generating normally distributed values. As a result, the loop code in this section designates just 500 iterations, but each iteration returns two normal deviate values for a total of 1,000 normal deviate values per set.
use DataScience
-- step 1
-- create a table for storing normal rand_decimals values
-- from 0.0, 0.1, 0.2...8.7, 8.8, 8.9
-- with a mean of 4.5 and a standard deviation of 1
drop table if exists ##rand_normal_decimals
create table ##rand_normal_decimals
(
nrand_decimal decimal(2,1)
)
-- step 2
-- declare and set variables
declare @pi float, @2pi float, @randNum1 float, @randNum2 float
declare @value1 float, @value2 float
declare @iteration int, @numberOfIterations int
declare @mean float
declare @stdDev float --standard deviation
declare @precision int --number of places to the right of the decimal point
select @iteration = 0
select @pi = pi()
select @2pi = 2.0 * @pi
select @mean = 4.5 -- specifies the mean for a normal distribution
select @stdDev = 1 -- specifies the standard deviation for a normal distribution
select @precision = 1
select @numberOfIterations = 500
---------------------------------------------------------------------------------
-- loop for number of iterations
-- each loop generates two random normal deviates
-- in x column of #rand_normal_decimals
while (@iteration < @numberOfIterations)
begin
select @randNum1 = rand()
select @randNum2 = rand()
select @value1 = round((sqrt(-2.0*log(@randNum1))*cos(@2pi*@randNum2))*@stdDev, @precision)+@mean
select @value2 = round((sqrt(-2.0*log(@randNum1))*sin(@2pi*@randNum2))*@stdDev, @precision)+@mean
insert into ##rand_normal_decimals (nrand_decimal) values (@value1)
insert into ##rand_normal_decimals (nrand_decimal) values (@value2)
select @iteration = @iteration + 1
end
--/*
-- step 3
-- original, SS rounded, and banker's rounded decimal values
select
nrand_decimal
,round(nrand_decimal,0) SS_nrand_decimal_nearest_int
,DataScience.dbo.bankersround(nrand_decimal) BR__nrand_decimal_nearest_int
from ##rand_normal_decimals
--*/
-- step 4
-- sums and comps for original, SS rounded, and banker's rounded decimal values
select
[sum of original decimal values]
,[sum of SS_nrand_decimal_nearest_int]
,[sum of BR__nrand_decimal_nearest_int]
,[sum of original decimal values] - [sum of SS_nrand_decimal_nearest_int] [SS round comparison]
,[sum of original decimal values] - [sum of BR__nrand_decimal_nearest_int] [BR round comparison]
from
(
-- sums for original, SS rounded, and Banker's rounded decimal values
select
sum(nrand_decimal) [sum of original decimal values]
,sum(SS_nrand_decimal_nearest_int) [sum of SS_nrand_decimal_nearest_int]
,sum(BR__nrand_decimal_nearest_int) [sum of BR__nrand_decimal_nearest_int]
from
(
-- original, SS rounded, and banker's rounded decimal values
select
nrand_decimal nrand_decimal
,round(nrand_decimal,0) SS_nrand_decimal_nearest_int
,DataScience.dbo.bankersround(nrand_decimal) BR__nrand_decimal_nearest_int
from ##rand_normal_decimals
) for_nrand_decimal_rounding_sums
) for_comps
-- step 5
go 5
Reviewing Selected Results from the T-SQL Code for Implementing the Framework
The objective of this subsection is to verify whether the trends for rounding decimal values with a normal distribution are consistent with those for rounding decimal values with a uniform distribution. The script in this section generates 1,000 normally distributed decimal values in a set versus the script in the preceding section, which generated 1,000 uniformly distributed decimal values in a set. A uniform distribution gives each decimal value from 0.0 through 8.9 an approximately even chance of occurring. In contrast, a normal distribution gives decimal values nearer to the mean a much greater chance of occurrence than those farther away from the mean. The combination of both of these distributions represents a wide range of distributions of commonly occurring decimal value distributions for analysis with SQL Server.
This section tracks two specific trends:
- Is it true that banker’s rounding rules only round up for decimal values with a digit of 5 after the decimal point when the integer part of the decimal value is odd? This section’s analysis answers the question by examining individual underlying decimal values from the beginning and ending of the 1,000 normally distributed decimal values from the preceding script.
- Are the sums of rounded values based on the banker’s rounding rule closer to the sums of unrounded decimal values than the sums of rounded decimal values based on the SQL Server round function? This section’s analysis answers the question by examining the sums across five sets of normally distributed decimal values and their rounded values by banker’s rounding rules versus their rounded values by the SQL Server round function.
Here is a screenshot of a spreadsheet populated with the first 20 rows from the first set of normally distributed decimal values and their corresponding rounded values.
- Again, the vast majority of rows (18 of 20) are not for underlying decimal values with a value of 5 immediately after the decimal point.
- For the two underlying decimal values that do have 5 immediately after the decimal point:
- Row 11, with a green-colored font, rounds up because the integer part of the decimal value is odd and
- Row 14, with a red-colored font, does not round up because the integer part of the decimal value is even.
Next is the screenshot of a spreadsheet populated with the bottom 20 rows from the first set of normally distributed decimal values and their corresponding rounded values.
- Again, only two rows have underlying decimal values with a 5 after the decimal point.
- The decimal value’s integer with an odd value in row 985 shows its banker’s rounded value rounded up.
- The decimal value’s integer with an even value in row 1000 shows its banker’s rounded value did not round up.
The results from the top and bottom screenshot rows for normally distributed values confirm the trend observed for uniformly distributed values.
- Underlying decimal values with a value of 5 after the decimal point are very rare.
- However, for these rare cases, banker’s rounding rules only round up about half the time, while SQL Server round function values round up for each underlying value.
The final screenshot in this tip compares, at an aggregate level, the sums across all five sets of normally distributed values.
- The values in Column A denote each of the five sets of normally distributed decimal values. The mean of the decimal values in this section is approximately the same as the mean of the decimal values in the prior section – namely, 4.5.
- Column B shows the sum for each set of normally distributed decimal values.
- Columns C and D show, respectively, the sums for decimal values rounded with the SQL Server round function and the bankersround function.
- Columns E and F show, respectively, the sum of the decimal values from Column A less the sum of the rounded decimal values by either the SQL Server round function or the bankersround function. The smaller the difference, the closer the rounded values are to the original decimal values. For all five comparisons, the differences are consistently smaller for the values rounded by the bankersround function. This confirms that the banker’s rounding rule returns values closer to their underlying values than the normal rounding rule implemented by the SQL Server round function.
Next Steps
For the examples in this tip, rounding by the banker’s rounding rule consistently returns sums with rounded values closer to the sums of the underlying values than the sums of the SQL Server round function values. Furthermore, these results are not materially impacted by the distribution of the underlying values.
From my perspective as a data analyst, the difference is enough to make me want to use the banker’s rounding rule implemented by the bankersround function in all applications requiring the rounding of decimal values to integers. On the other hand, more people are familiar with normal rounding rules implemented by the SQL Server round function than banker’s rounding rules. Therefore, you may want to get the concurrence of your client for a set of results with rounding before implementing banker’s rounding rules instead of the SQL Server round function.
You can adapt the framework in this example for the data with which you are working to help your client understand the impact of various rounding rules. If the sample data are particularly large, say millions or hundreds of millions of decimal values, a random sample of just five thousand-value sets may be sufficient to verify if the bankersround function returns rounded values that are closer in the aggregate to their underlying values than SQL Server round function values to their underlying values. In addition, you can assess how much closer the underlying values are to the banker’s rounded values than the SQL Server round function values.
Finally, I want to make you aware that MSSQLTips.com published an earlier tip with the banker’s rounding rule for rounding decimal values to the nearest penny instead of the nearest integer. You may find this tip helpful if you need to round to a different level than the nearest integer.
