I had an email from a friend today where he asked what was wrong with SQL Server, and wondered if ROUND() didn't go to the same school that he did.
The example was:
|
1 2 3 4 5 |
SELECT '0.5' [SOURCE VALUE], '1' [SHOULD ROUND TO], ROUND('0.5',0) [SQL SAYS] UNION SELECT '1.55', '1.6', ROUND('1.55',1) UNION SELECT '94.55', '94.6', ROUND('94.55',1); |
The output was:
What was puzzling him is why SQL Server's ROUND was converting 94.55 to 94.5 and not 94.6. Now writing numbers as strings is problematic to start with, but that aside, he was puzzled by the output.
Rounding
There isn't just one "correct" way to do rounding. Here is info on it: https://en.wikipedia.org/wiki/Rounding
The T-SQL ROUND() function does apply "standard" mathematical rounding. Values from 5 and up in the next digit go to the next value, less than 5 goes down. So you'd expect 94.55 to be 94.6 not 94.5.
It's worth noting that not all languages do this. Some languages implement Bankers' Rounding. This was designed to avoid the skew that you can get if you have a whole lot of .5 values. For example, 2.5 + 3.5 + 4.5 would be 3 + 4 + 5 = 12 if you rounded them all to integers first. With Bankers' rounding, it alternates. 0.5 goes to 0, 1.5 goes to 2, 2.5 also goes to 2, 3.5 goes to 4, etc. And so the 2.5 + 3.5 + 4.5 would be 2 + 4 + 4 which is 10, and closer to the underlying total of 10.5.
Older versions of VB, etc. used Banker's Rounding, but that's not what the T-SQL ROUND() function does.
So what's up with ROUND() ?
In that case, what's the problem with his original query?
The issue is data types. He didn't provide decimal values to round, he provided strings. That means they have to go to another data type before they are rounded. Which one?
If I execute this code, you might see the problem:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
USE tempdb; GO DROP TABLE IF EXISTS dbo.GregTemp; SELECT '94.55' AS SourceValue, '94.6' AS ShouldRoundTo, ROUND('94.55',1) AS RoundedValue INTO dbo.GregTemp; SELECT * FROM dbo.GregTemp; SELECT c.[name] AS ColumnName, t.[name] AS DataType FROM sys.columns AS c INNER JOIN sys.types AS t ON t.system_type_id = c.system_type_id AND t.user_type_id = c.user_type_id WHERE c.object_id = OBJECT_ID(N'dbo.GregTemp'); DROP TABLE IF EXISTS dbo.GregTemp; |
as it returns this:
Note that the value is a float. And as I've talked about in this blog many times, floats are evil for business applications, and a common mistake.
To see the difference, instead of using the implicit conversion, try this:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SELECT '94.55' AS SourceValue, '94.6' AS ShouldRoundTo, CAST('94.55' AS decimal(18, 1)) AS RoundedValue INTO dbo.GregTemp; SELECT * FROM dbo.GregTemp; SELECT c.[name] AS ColumnName, t.[name] AS DataType FROM sys.columns AS c INNER JOIN sys.types AS t ON t.system_type_id = c.system_type_id AND t.user_type_id = c.user_type_id WHERE c.object_id = OBJECT_ID(N'dbo.GregTemp'); DROP TABLE IF EXISTS dbo.GregTemp; |
And that returns this output:
Note that the rounded value is the expected value, and the output data type is the expected decimal type.
Finally, also keep in mind what when SQL Server Management Studio shows you a float value, it also rounds it before it shows it to you. So a value like 9.9999999999999999 might appear as 10.0 but it's not 10.0.
Hope this helps someone.
Hey Greg, why are you stubbornly using string literals instead of numbers? I've done your exercise from Facebook's screen using number literals and the result is different.
If you're encouraging people to write numbers as strings, this is bad practice. Performance is worse and it's just asking for problems.
use tempdb;
go
drop table if exists dbo.AnonTemp;
select 94.55 as SourceValue,
94.6 as ShouldRoundTo,
round(94.55, 1) as RoundedValue
into dbo.AnonTemp;
select * from AnonTemp;
Well "anon", I fear you've missed the point of the post. I had a friend ask why this was happening. It was his code, and he didn't follow the output that was produced.
I can't see anywhere that it's actually encouraging people to write numbers as strings. It's just what the code provided had.
The highlight of reading your post, Greg, is Annon's comment! Yes, Greg, why are you stubbornly using the string data type? Rest assured, I got your point!
Thanks ! From various comments in various places, it's very clear to me that people comment after looking at the picture, and without actually reading the post. I'm wondering if I avoid that by making the picture something else but code.