Compare SQL Round to Bankers Rounding for Millions of Decimal Values

Problem

Please show how to implement bankers rounding with a SQL script for a table with millions of rows having decimal values. Also, compare the results of bankers rounding versus the built-in SQL Server round function, which implements traditional rounding.

Solution

Rounding is a process for approximating a value with n digits by a second value of less than n digits. Rounded values are used because the rounded values are easier to communicate, and they may also simplify subsequent calculations at the expense of a modest degree of rounding error. When rounding, insignificant digits are those to the right of the last digit of the rounded value. You can compute a ratio between 0 and 1 as the value of the digits to the right of the least significant digit divided by 10 raised to the number of insignificant digits as a guide to rounding values.

Two Ways to do Rounding in SQL Server

In SQL Server, two of several ways to implement rounding:

1. Traditional Rounding: The least significant digit is unconditionally rounded up by one when the computed ratio is greater than or equal to .5.
2. Bankers Rounding: The least significant digit of the rounded value is
   • Rounded up by one when the computed ratio is greater than .5, or
   • Rounded up by one only when the computed ratio equals .5 and the least significant digit is odd, or
   • Not rounded up by one when the computed ratio equals .5 and the least significant digit is even, or
   • Not rounded up when the ratio is less than .5.

Most SQL Server professionals and their clients are familiar with traditional rounding, i.e., whenever you round one or more numeric values with the SQL Server round function. Jeremy Kadlec authored a tip that conveys the basics of using the SQL Server round function.

Several other MSSQLTips.com articles (“Rounding Numbers to the Nearest Integer with T-SQL“, “Rounding to the Nearest Penny in SQL Server with T-SQL“, and “SQL ROUND Function vs. Bankers Rounding with T-SQL“) have introduced bankers rounding via T-SQL as an alternative to the SQL Server round function. Although bankers rounding requires a T-SQL custom solution, bankers rounding persists as a viable rounding technique because it consistently returns fairer and more accurate results than traditional rounding. If fair, accurate rounding is important in your applications, you may want to learn more about bankers rounding.

Creating a Table with Millions of Random Decimal Values

This tip’s first major section presents a script for generating 2.4 million random decimal values. By comparing the rounded values based on traditional rounding or bankers rounding to the unrounded decimal values, it can be assessed which rounding technique is most accurate and unbiased. Adaptations of the script may serve other use cases in which you require a large set of random values. If you do not care to learn how to shuffle a large set of decimal values, you can just scan this section to learn about the structure of the table storing random decimal values to be rounded by either the SQL Server round function or a custom T-SQL script implementing bankers rounding.

The crypt_gen_random function can generate the most unpredictable random values in SQL Server. The primary purpose of this function is to generate passwords for encrypting and decrypting clear text to cipher text, so the clear text is secure from those who do not have the password. Here, the function will be used to shuffle a set of integers into a random order. The randomly ordered integers are then multiplied by .000001 to return randomly ordered decimal values in the range of 0.000001 through 2.400000. Shuffling values can be useful in many contexts, including confirming whether a solution works with any order of values, randomizing options at different steps in a game to provide different experiences each time a game is played, and distributing tasks randomly across servers to balance load and optimize performance.

Script for shuffling decimal values

The following script for shuffling decimal values relies on two global temporary tables (##forUniqueRandomIntegers and ##forShuffledNumbers).

• ##forUniqueRandomIntegers table has two columns:
  • RowID: Stores integer values in the range of 1 through 2,400,000. These values are generated by the row_number function for enumerating rows from three cross joins of the system.objects view with itself. The top 2.4 million integer values from the results set are saved in this column.
  • RandomOrder: An update statement invokes the crypt_gen_random function to assign random varbinary values to this column in the table.
• ##forShuffledNumbers table has three columns:
  • RandomOrder: A varbinary data type.
  • RandomInteger: An int data type.
  • RandomDecimal: A decimal(7,6) data type.
• The rows of the ##forShuffledNumbers table are populated with an insert into statement based on a select statement from the ##forUniqueRandomIntegers table:
  • RandomOrder column in ##forShuffledNumbers is populated from the RandomOrder column in ##forUniqueRandomIntegers.
  • RandomInteger column in ##forShuffledNumbers is populated from the RowID column in ##forUniqueRandomIntegers.
  • RandomDecimal column in ##forShuffledNumbers is populated from an expression that multiplies RowID column values from ##forUniqueRandomIntegers by .000001.

-- create fresh versions of two temporary tables to
-- successively create random integers in the 
-- range from 1 through 2,400,000
 
drop table if exists ##forUniqueRandomIntegers;
drop table if exists ##forShuffledNumbers;
 
-- create a temporary table to store numbers from a row_number function
create table ##forUniqueRandomIntegers (
    RowID INT,
    RandomOrder VARBINARY(8)
);
 
 
-- use a CTE to generate numbers from 1 to 2,400,000
-- based on two cross joins of objects in the sys.objects view
with Numbers as (
    select top 2400000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowID
    from sys.objects s1
    cross join sys.objects s2
    cross join sys.objects s3
)
-- insert these numbers into the temporary table
insert into ##forUniqueRandomIntegers (RowID)
select RowID
from Numbers;
 
-- populate RandomOrder column with the crypt_gen_random function
update ##forUniqueRandomIntegers
set RandomOrder = crypt_gen_random(8);
 
-- display top 10 rows ordered by RowID
-- from ##forUniqueRandomIntegers
select top 10 * from ##forUniqueRandomIntegers order by RowID;
 
-- display bottom 10 rows ordered by RowID
-- from ##forUniqueRandomIntegers
select * from
(select top 10 * from ##forUniqueRandomIntegers order by RowID desc) [bottom 10 rows]
order by RowID ASC;
 
-- create another temporary table(#ShuffledNumbers) to store random values 
-- from the crypt_gen_random function with random values having an int data type
create table ##forShuffledNumbers (
    RandomOrder varbinary(8) primary key,
    RandomInteger INT,
    RandomDecimal Decimal(7,6)
);
 
-- populate ##forShuffledNumbers
-- RandomOrder controls the shuffle order, 
-- RandomInteger values are the shuffled integer values
insert into ##forShuffledNumbers (RandomOrder,RandomInteger,RandomDecimal)
select RandomOrder, RowID, cast(RowID*.000001 as decimal(7,6))
from ##forUniqueRandomIntegers order by RowID;
 
-- display shuffled random integers
select RandomInteger, RandomDecimal from ##forShuffledNumbers
 
-- compute count, min, and max values for integer and decimal value
select
 count(*) [count of integers]
,min(RandomInteger) [minimum RandomInteger]
,max(RandomInteger) [maximum RandomInteger]
,min(RandomDecimal) [minimum RandomDecimal]
,max(RandomDecimal) [maximum RandomDecimal]
from ##forShuffledNumbers

Results from above SQL Script

Selected results sets from the preceding script are presented below:

• First and Second Results Sets: Display, respectively, the top and bottom 10 rows from the ##forUniqueRandomIntegers table.
  • The RowID column values start with 1 and end with 2400000.
  • The RandomOrder column is populated with hexadecimal values. There are two hexadecimal values for each of the eight bytes per row returned by the crypt_gen_random function. Each run of the preceding script will return a different set of random hexadecimal values for each row in RandomOrder.
• Third Results Set: Shows the top 10 rows from the RandomInteger and RandomDecimal columns from the ##forShuffledNumbers table. Notice that the RandomInteger values are shuffled from the progressively ordered values in the RowID column of the ##forUniqueRandomIntegers table.
• Fourth Results Set: Displays the count of RandomInteger values from the ##forShuffledNumbers table along with the minimum and maximum RandomInteger and RandomDecimal values.

Rounding Values with SQL Round and Bankers Rounding

This section presents and reviews the T-SQL code to populate the following tables:

• ##RandomDecimal_after_bankers_rounding: Stores the results of rounding the RandomDecimal column values from the ##forShuffledNumbers table based on bankers rounding rules.
• ##RandomDecimal_after_SQLRound: Demonstrates the application of the SQL Server built-in round function to the same source column values from the same source table.

Both rounding techniques round RandomDecimal column values to three places after the decimal point.

The following script excerpt begins by dropping the ##RandomDecimal_after_bankers_rounding table and ##RandomDecimal_after_SQLRound table. This allows the subsequent code in the script to create fresh versions of each table based on the current values in the ##forShuffledNumbers table. Each time you re-run the code presented in the preceding section, you will generate a fresh version of the ##forShuffledNumbers table.

The first select statement from the script excerpt below illustrates an approach for transforming the RandomDecimal column values from the ##forShuffledNumbers table based on bankers rounding rules to a decimal value with three places after the decimal. Towards the bottom of the select statement, you can see a subquery named for_rounding_comps. This subquery returns a results set to the select statement in which it is embedded. The returned columns are from code inside the subquery as well as from the ##forShuffledNumbers table.

• row_number is defined by a row_number function that assigns a unique, sequential integer value to each successive row in the results set.
• RandomDecimal denotes the RandomDecimal column values extracted from the ##forShuffledNumbers table
• [RandomDecimal as varchar] is the RandomDecimal value for the current row converted to a varchar string format. The varchar field has a length of eight because the decimal value has a precision of 7 digits plus one character for the decimal point.
• [right 3 digits in RandomDecimal] is another string in varchar format with the right three digits in RandomDecimal.

The outer query in the first select statement develops other intermediate values and combines them in a case statement to populate the [RandomDecimal after round to 3 places] column in its results set. This final column contains the rounded value of RandomDecimal to three places after the decimal point based on bankers rounding rules.

There are three conditions in the case statement that are mutually exclusive and exhaustive for the values being processed:

• The first when clause rounds up by .001 because
  • The last three digits in RandomDecimal are precisely 500, and
  • The third digit after the decimal point is odd (another name for bankers rounding is round-to-even rounding).
• The second when clause also rounds up by .001 because the right three digits in RandomDecimal are greater than 500.
• The else clause responds to the case when neither preceding when clause criterion is satisfied. This case is specified by:
  • The last three digits in RandomDecimal being precisely 500, but
  • The third digit after the decimal point is even.
  • Notice there is no rounding up in this final case.

-- conditionally drop two temporary tables to
-- successively create random integers in
-- ##RandomDecimal_after_bankers_rounding and ##RandomDecimal_after_SQLRound
 
drop table if exists ##RandomDecimal_after_bankers_rounding;
drop table if exists ##RandomDecimal_after_SQLRound;
 
 -- banker's rounding for RandomDecimal to 3 places after the decimal point
 -- saved in ##RandomDecimal_after_bankers_rounding
 select
  *
into ##RandomDecimal_after_bankers_rounding
from(
-- columns from for_rounding_comps subquery
-- plus [right 3 of RandomDecimal as int value], [digit b4 right 3 in RandomDecimal as int value],
-- [digit b4 right 3 odd (1) or even(0)], [RandomDecimal b4 round to 3 places],
-- [RandomDecimal after round to 3 places] 
select
 *
,cast([right 3 digits in RandomDecimal] as int) [right 3 of RandomDecimal as int value]
,cast(substring([RandomDecimal as varchar],5,1) as int) [digit b4 right 3 in RandomDecimal as int value]
,(cast(substring([RandomDecimal as varchar],5,1) as int) % 2) [digit b4 right 3 odd (1) or even(0)]
,(cast(substring([RandomDecimal as varchar],1,5) as decimal(4,3))) [RandomDecimal b4 round to 3 places]
,case
 when cast([right 3 digits in RandomDecimal] as int)  = 500 then
     (cast(substring([RandomDecimal as varchar],1,5) as decimal(4,3))) 
      + (.001)*(cast(substring([RandomDecimal as varchar],5,1) as int) % 2)
 when cast([right 3 digits in RandomDecimal] as int)  > 500 then
     (cast(substring([RandomDecimal as varchar],1,5) as decimal(4,3))) + (.001)
 
 else (cast(substring([RandomDecimal as varchar],1,5) as decimal(4,3)))
 end [RandomDecimal after round to 3 places]
from(
-- row_number, RandomDecimal,RandomDecimal as varchar, right 3 of RandomDecimal
select
 row_number() over (order by (select null)) AS row_number
,RandomDecimal
,cast(RandomDecimal as varchar(8)) [RandomDecimal as varchar]
 
,right(cast(RandomDecimal as varchar(8)),3) [right 3 digits in RandomDecimal]
from ##forShuffledNumbers
 ) for_rounding_comps
) bankers_rounding_for_3_places
 
 
-- optionally echo ##RandomDecimal_after_bankers_rounding
select * 
from ##RandomDecimal_after_bankers_rounding

The next script excerpt for this section illustrates how to perform traditional rounding with the SQL round function. This script is much shorter than the preceding script as it takes advantage of the built-in round function. The following code sample populates three columns in the ##RandomDecimal_after_SQLRound table. The most important columns are named RandomDecimal and SQLRound. The code rounds RandomDecimal to three places after the decimal. By comparing the values in these two columns you can confirm how traditional rounding works – namely, it rounds up whenever the last three digits in the RandomDecimal value for a row is 500 or greater.

-- row_number, RandomDecimal, SQLRound
select
 row_number() over (order by (select null)) AS row_number
,RandomDecimal
,round(RandomDecimal,3) SQLRound
into ##RandomDecimal_after_SQLRound
from ##forShuffledNumbers
 
-- optionally echo ##RandomDecimal_after_SQLRound
select * 
from ##RandomDecimal_after_SQLRound

Comparing Rounded Values from SQL Round and Bankers Rounding

For this tip, there are several ways to compare the rounded decimal numbers both within and across the two rounding techniques implemented by the code:

• First, examine how the rounded RandomDecimal values are rounded by focusing on the three criteria for bankers rounding.
• Second, examine how the rounded RandomDecimal values are rounded by observing the slightly different set of three criteria for traditional rounding with the SQL Server round function.
• Third, see how close rounded RandomDecimal values are to the unrounded RandomDecimal values with bankers rounding versus traditional rounding with the SQL Server round function.

Selected Results for Rounding with Bankers Rounding

The script for this subsection displays excerpts with rounded values for three criteria sets with bankers rounding from the ##RandomDecimal_after_bankers_rounding table. Each criterion is evaluated with a separate select statement. The results sets are merged into one results set with a union operator between the first and second select statements as well as between the second and third select statements.

• The first select statement has no explicit criteria set. However, this select statement extracts only the top two rows with a top 2 phrase following the select keyword. Neither of these rows have the criteria in either of the other two select statements.
• The second criteria set has two criteria:
  • The right 3 digits in RandomDecimal are 500, and
  • The candidate digit in RandomDecimal to be rounded is odd.
• The third criteria set also has two criteria:
  • The right 3 digits in RandomDecimal are 500, but
  • The candidate digit in RandomDecimal to be rounded is even.

--Selected rows from ##RandomDecimal_after_bankers_rounding
select top 2  
RandomDecimal
,[right 3 of RandomDecimal as int value]
,[digit b4 right 3 odd (1) or even(0)]
,[RandomDecimal after round to 3 places] 
from ##RandomDecimal_after_bankers_rounding
 
union
 
select top 2 
RandomDecimal
,[right 3 of RandomDecimal as int value]
,[digit b4 right 3 odd (1) or even(0)]
,[RandomDecimal after round to 3 places] 
from ##RandomDecimal_after_bankers_rounding
where [right 3 of RandomDecimal as int value] = 500
and ([digit b4 right 3 odd (1) or even(0)] = 1)
 
union
 
select top 2 
RandomDecimal
,[right 3 of RandomDecimal as int value]
,[digit b4 right 3 odd (1) or even(0)]
,[RandomDecimal after round to 3 places] 
from ##RandomDecimal_after_bankers_rounding
where [right 3 of RandomDecimal as int value] = 500
and ([digit b4 right 3 odd (1) or even(0)] = 0)
order by [right 3 of RandomDecimal as int value]
,[digit b4 right 3 odd (1) or even(0)]

Results from above script

Here is the results set from the preceding script:

• The top and bottom rows in the results set do not have 500 as the right three digits in RandomDecimal. Therefore, these rows are for the first criteria set. In the case of the top row, the right three digits have a value of less than 500 – namely, 499. In the case of the bottom row, the right three digits have a value that is greater than 500 – namely 709. Therefore, these two rows are rounded strictly on the value of the right three digits in RandomDecimal:
  • First Row: A value of less than 500, the unrounded Decimal value of 1.065499 rounds to 1.065.
  • Last Row: A value of greater than 500, the unrounded Decimal value of 1.468709 rounds to 1.469.
• The fourth and fifth rows in the results set both have 500 as the right three digits in RandomDecimal as well as an odd digit in RandomDecimal immediately before the right three digits. Therefore,
  • Fourth Row: The RandomDecimal value of 0.409500 rounds up to 0.410, and
  • Fifth Row: The RandomDecimal value of 0.667500 rounds up to 0.668.
• The second and third rows in the results set both have 500 as the right three digits in Random Decimal but an even digit in RandomDecimal immediately before the right three digits. Therefore,
  • Second Row: The RandomDecimal value of 0.674500 rounds down to 0.674, and
  • Third Row: The RandomDecimal value of 2.302500 rounds down to 2.302.

Selected Results for Rounding with the SQL Server Round Function

The script for this subsection displays excerpts for rounded values for three criteria sets with traditional rounding from the ##RandomDecimal_after_SQLRound table. This table was populated based on the SQL Server round function. As with the preceding script, the next code excerpt extracts three pairs of rows where each pair of rows is extracted by a different criterion.

• The first pair of rows is derived with a criterion for not rounding up based on the right three digits in RandomDecimal being less than 500. This satisfies the traditional rounding rule for not rounding up.
• The criterion for the second select statement extracts the first two rows having the right three digits in RandomDecimal precisely equal to 500.
• The criterion for the third select statement extracts the first two rows having the right three digits in RandomDecimal being greater than 500.

-- Selected rows from  ##RandomDecimal_after_SQLRound
select top 2 
 RandomDecimal
,right(cast(RandomDecimal as varchar(8)), 3) [right 3 of RandomDecimal as varchar value]
,SQLRound  
from ##RandomDecimal_after_SQLRound
where cast(right(cast(RandomDecimal as varchar(8)),3) as int) < 500
 
union
 
select top 2  
 RandomDecimal
,right(cast(RandomDecimal as varchar(8)), 3) [right 3 of RandomDecimal as varchar value]
,SQLRound  
from ##RandomDecimal_after_SQLRound
where cast(right(cast(RandomDecimal as varchar(8)),3) as int) = 500
 
union
 
select top 2  
 RandomDecimal
,right(cast(RandomDecimal as varchar(8)), 3) [right 3 of RandomDecimal as varchar value]
,SQLRound  
from ##RandomDecimal_after_SQLRound
where cast(right(cast(RandomDecimal as varchar(8)),3) as int) > 500
order by right(cast(RandomDecimal as varchar(8)), 3)

Here is the results set from the preceding script.

• First Pair of Rows: Does not round up because traditional rounding dictates that no rounding up occurs when the right three digits in RandomDecimal are less than 500.
• Second Pair of Rows: Rounds up from the RandomDecimal value in the third place after the decimal point. This is because traditional rounding dictates rounding up occurs whenever the right three digits in RandomDecimal is 500 or greater, and the right three digits for these two rows are precisely 500.
• Third Pair of Rows: Rounds up the RandomDecimal value in the third place after the decimal point. This is because the right three digits in RandomDecimal are greater than 500.

Are Bankers Rounded Values Closer to RandomDecimal Values Than SQL Server Round Function Values?

One objective of this tip is to demonstrate a framework for comparing results from rounding decimal values in a database by a T-SQL script that implements bankers rounding versus the SQL Server round function. This subsection responds to that objective. Prior sections in this tip illustrate how to create random decimal values in a database as well as how to round the decimal values in a database.

Step 1.The first step in the code for the comparison is to join the decimal values and rounded values from two separate tables (##RandomDecimal_after_bankers_rounding and ##RandomDecimal_after_SQLRound). The name of the joined table in the code below is ##AllRandomDecimalSumofSQLRoundSumofBankersRound. Two computed columns are added to the joined table.

• RandomDecimal_less_SQLRound: Computed as RandomDecimal minus the rounded value of RandomDecimal by the SQL Server round function.
• RandomDecimal_less_bankers_round_to_3_places: Computed as RandomDecimal minus the rounded value of RandomDecimal by bankers rounding technique.

Here is the script for the first step:

drop table if exists ##AllRandomDecimalSumofSQLRoundSumofBankersRound;
 
-- script to create ##AllRandomDecimalSumofSQLRoundSumofBankersRound
select 
 ##RandomDecimal_after_bankers_rounding.row_number
,##RandomDecimal_after_bankers_rounding.RandomDecimal
,[right 3 of RandomDecimal as int value]
,[RandomDecimal after round to 3 places]
,##RandomDecimal_after_SQLRound.SQLRound
,(##RandomDecimal_after_bankers_rounding.RandomDecimal 
  - ##RandomDecimal_after_SQLRound.SQLRound) RandomDecimal_less_SQLRound
,(##RandomDecimal_after_bankers_rounding.RandomDecimal 
  - [RandomDecimal after round to 3 places]) RandomDecimal_less_bankers_round_to_3_places
into ##AllRandomDecimalSumofSQLRoundSumofBankersRound
from ##RandomDecimal_after_bankers_rounding
join ##RandomDecimal_after_SQLRound
on ##RandomDecimal_after_bankers_rounding.row_number = ##RandomDecimal_after_SQLRound.row_number
 
-- optionally display rows in ##AllRandomDecimalSumofSQLRoundSumofBankersRound
select * from ##AllRandomDecimalSumofSQLRoundSumofBankersRound

Each computed column in the table contains rounded values using a different technique.

Step 2. The next step in the comparison is to compute a sum of the differences between the decimal values and the rounded values for each rounding method.

• [sum of RandomDecimal_less_SQLRound]
  • The sum of the differences between the decimal values and the SQL Server round function return values.
  • Reflects the magnitude of the differences between the decimal values and the rounded values by the SQL Server round function.
• [sum of RandomDecimal_less_bankers_round_to_3_places]
  • The sum of the differences between the decimal values and the rounded values by the bankers rounding technique.
  • Reflects the magnitude of the differences between the decimal values and the rounded values by the bankers rounding technique.
• The smaller each of these sums are, the greater the degree to which the rounded values match the underlying decimal values. Therefore, the smaller the sum, the better the degree to which the rounded values approximate their underlying decimal values.

The sole difference between traditional rounding with the SQL Server round function and the bankers rounding technique is the rounding for the last three decimal digits when they are precisely 500. In this case, traditional rounding always rounds up. In contrast, bankers rounding rounds up only in half of the cases – namely, when the last digit to be rounded is odd. This style of rounding for traditional rounding introduces a small bias relative to bankers rounding. The traditional rounding style results in slightly larger rounded values than the bankers rounding style.

In the sample of RandomDecimal values used in this tip, there are a total of 2.4 million decimal values, but only 2400 of these values have precisely 500 in their right three digits. The following script shows the T-SQL for computing the sum of RandomDecimal values either traditionally rounded values or bankers rounding values. Furthermore, the script is run twice – once for the whole sample of 2.4 million decimal values and a second time for only the 2400 decimal values with 500 as the value for the right three digits.

-- count of RandomDecimal, sum of RandomDecimal less SQLRound,
-- sum of RandomDecimal less bankers round to 3 places
-- for all rows in ##AllRandomDecimalSumofSQLRoundSumofBankersRound
select 
 count(*) [count of RandomDecimal]
,sum(RandomDecimal_less_SQLRound) [     ]
,sum(RandomDecimal_less_bankers_round_to_3_places) [sum of RandomDecimal_less_bankers_round_to_3_places] 
from ##AllRandomDecimalSumofSQLRoundSumofBankersRound
 
 
-- count of RandomDecimal, sum of RandomDecimal less SQLRound,
-- sum of RandomDecimal less bankers round to 3 places
-- for ##AllRandomDecimalSumofSQLRoundSumofBankersRound rows 
-- with [right 3 of RandomDecimal as int value] = 500
select 
 count(*) [count of RandomDecimal]
,sum(RandomDecimal_less_SQLRound) [sum of RandomDecimal_less_SQLRound]
,sum(RandomDecimal_less_bankers_round_to_3_places) [sum of RandomDecimal_less_bankers_round_to_3_places] 
from ##AllRandomDecimalSumofSQLRoundSumofBankersRound
where [right 3 of RandomDecimal as int value] = 500

Here are the results sets from the two preceding select statements. The top panel is for the full 2.4 million rows, and bottom panel is for the 2400 rows with 500 as the value for the right three digits.

• For both panels, the sum of RandomDecimal_less_bankers_round_to_3_places is less than the sum of RandomDecimal_less_SQLRound.
  This outcome confirms that bankers rounding more closely approximates the underlying decimal value than the SQL Server rounding function.
• In addition, the sum of RandomDecimal_less_SQLRound is -1.200000 in both the top and bottom panels.
  This outcome confirms all the differences in rounding for the two techniques is for cases where the right three decimal digit values are 500.

Next Steps

This tip’s chief objective is to confirm that rounding decimal values to three places after the decimal point is more accurate and less biased with bankers rounding than with the built-in SQL Server round function. A similar outcome was confirmed in a prior tip when comparing rounding decimal values with one digit after the decimal point to the nearest integer value.

The SQL Server round function implements traditional rounding instead of bankers rounding. Traditional rounding is inherently biased because it rounds up more often than bankers rounding. This is because traditional rounding always rounds up when the insignificant digits in the underlying values are midway between the next higher value and the next lower value. In contrast, bankers rounding rounds up half the time and rounds down the other half of the time when the insignificant digits in its underlying values are midway between the next higher value and the next lower value.

One potential solution for fixing the biased rounding of the built-in SQL Server round function is for Microsoft to update the built-in SQL Server round function with a parameter that enables the choice of either traditional rounding or bankers rounding. This may be a viable solution because Microsoft already updated the built-in round function in SQL Server 2012 to enable truncation instead of traditional rounding via a third parameter. I am unaware of any empirical investigations comparing outcomes from the SQL Server round function with traditional rounding via the versus truncation.

In any event, traditional rounding is clearly not the best choice when highly accurate and unbiased rounding outcomes are required. Computing scenarios like this include:

• Calculating interest or totals on currency values.
• Calculating bills and invoices.
• Statistical analysis for very large datasets where the unbiased nature of bankers rounding can avoid the skew associated with rounded values from traditional rounding.

If you regularly perform any of the above calculations with T-SQL in SQL Server, your next step should be to learn more about bankers rounding. The introduction to this tip includes links for learning more about bankers rounding in SQL Server. Also, you may transfer projects where biased rounding is unacceptable to other programming environments besides Microsoft SQL Server. Microsoft Copilot states that Python, the BigDecimal class in Java, and the Math.Round method for C# in the .NET Framework are programming environments that round via bankers rounding by default. (The prompt to Microsoft Copilot was “what programming environments have bankers rounding as the default way of rounding?”.)

Finally, if you must perform bankers rounding for an application that uses T-SQL, you may be able to use the current tip as is or adapt the example in this tip. Another approach may be to apply either of these two tips (“Rounding Numbers to the Nearest Integer with T-SQL“, “Rounding to the Nearest Penny in SQL Server with T-SQL“) when rounding to the nearest integer or penny with T-SQL.