During the development of a unique data analytics application we migrated a large collection of data from Mirosoft Access to Microsoft SQL Server. As we went through validation, we were finding significantly different results. In the end, we discovered the issue was that Access using a rounding technique called “Banker’s Rounding”.
SQL Server uses a more traditional rounding technique where:
- Anything below .5 rounds down to the previous whole number. For example, 2.1 becomes 2.0, 3.4 becomes 3.0
- Numbers over .4 rounds up to the next whole number. 4.7 becomes 5.0 and 9.5 becomes 10.
Access uses a unique method called Bankers Rounding. This is an algorithm in which numbers which are equidistant from the two nearest integers are rounded to the nearest even integer. Using our examples above:
- In this case 2.1 rounds down to 2; 3.4 rounds up to 4.
- And, 4.7 rounds down to 4 and 9.5 becomes 10.
A quick search of the internet will show several smart people have implemented Bankers Rounding for SQL Server. This can be add to SQL as a user defined function and called just like the ROUND() function.
I’m not sure who invented Bankers Rounding but it was designed to remove bias. I assume this is why Microsoft chose this implementation for Access. But, it’s a good lesson in how different platforms can produce different results.