August 17, 2010 at 8:53 am
Thanks for the question Hugo. Nice continuation in the series.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 17, 2010 at 10:25 am
So would it be fair to say that the third argument of ROUND, function, actually ensures you round down instead of up, if you set it to a non zero value?
August 17, 2010 at 11:50 am
jts_2003 (8/17/2010)
So would it be fair to say that the third argument of ROUND, function, actually ensures you round down instead of up, if you set it to a non zero value?
Nope. It truncates and then does the rounding. ROUND(5/3.0, 3,2) would have produced a result of 1.67 (up from the nominal value of 1.666666).
August 17, 2010 at 2:45 pm
jts_2003 (8/17/2010)
So would it be fair to say that the third argument of ROUND, function, actually ensures you round down instead of up, if you set it to a non zero value?
No. The third argument is to truncate instead of rounding. Rounding can be both up and down (when rounding to the integer, 1.7 will be rounded up but 1.3 will be rounded down). But truncation can also have the effect of rounding either up or down - truncating 1.7 will result in 1 (down), but truncating -1.7 will result in -1 (up).
To round up or down, use CEILING() and FLOOR(). To round according to normal rounding rules, use ROUND() with the default function. And to truncate, use ROUND() with the non-default function.
john.moreno (8/17/2010)
Nope. It truncates and then does the rounding. ROUND(5/3.0, 3,2) would have produced a result of 1.67 (up from the nominal value of 1.666666).
The ROUND() function with the non-zero function actually only truncates. The result of ROUND(5/3.0, 3, 2) is 1.666000 - truncated to the third decimal. This can be verified by running SELECT ROUND(5/3.0, 3, 2);
When you assign that result to a variable that is declared to have two decimal places (as in the code used in the QotD), the assignment forces an implicit conversion. Normal rounding rules apply, so the result will then be 1.67. But that is not related to the ROUND() function itself.
August 25, 2010 at 8:19 am
Coool question, Hugo.. Thanks 🙂
August 29, 2010 at 7:14 am
Agreed - Hugo, great question. I'm fascinated with the way the round works in SQL.
Jamie
September 12, 2010 at 6:22 am
I think there's a small error in the explanation here. Round never returns a result of type decimal(8,6) because every decimal result that it returns has precision 38 - the datatype for decimal and numeric types is changed by round to decimal(38,s) where s is the scale of the original type. This is documented quite clearly in BoL (http://msdn.microsoft.com/en-us/library/ms175003.aspx). So it will return a value of type decimal(38,6) which is then implicitly converted to decimal(5,2) for the assignment.
Tom
September 12, 2010 at 6:42 am
Tom.Thomson (9/12/2010)
I think there's a small error in the explanation here. Round never returns a result of type decimal(8,6) because every decimal result that it returns has precision 38 - the datatype for decimal and numeric types is changed by round to decimal(38,s) where s is the scale of the original type. This is documented quite clearly in BoL (http://msdn.microsoft.com/en-us/library/ms175003.aspx). So it will return a value of type decimal(38,6) which is then implicitly converted to decimal(5,2) for the assignment.
Hi Tom,
Thanks for catching that. I can not believe I actually overlooked that (though the hyperlink is damaged, I did include a link to that article in the explanation).
However, I did not just post without checking. Here is how I checked my statement about the data type returned by the ROUND function:
SELECT ROUND(5/3.0, 1, 2) AS a
INTO b;
go
EXEC sp_help b;
go
DROP TABLE b;
go
On SQL Server 2005, the output of sp_help indicates that the column is typed as numeric(8,6), not numeric(38,6). I cannot check this on SQL Server 2008, as I don't have that version installed on this computer. Maybe you can execute this code on SQL 2008 and report back the results?
(Of course, for this particular question it does not really matter; there is no overflow so the numeric(38,6) would just add 30 extra leading zeroes and not affect the result - but now that you found this, I do want to go to the bottom of it;-))
September 12, 2010 at 1:16 pm
Hugo Kornelis (9/12/2010)
However, I did not just post without checking. Here is how I checked my statement about the data type returned by the ROUND function:
SELECT ROUND(5/3.0, 1, 2) AS aINTO b;
go
EXEC sp_help b;
go
DROP TABLE b;
go
On SQL Server 2005, the output of sp_help indicates that the column is typed as numeric(8,6), not numeric(38,6). I cannot check this on SQL Server 2008, as I don't have that version installed on this computer. Maybe you can execute this code on SQL 2008 and report back the results?
)
that intrigued me, so I did some more documentation checking.
It appears that there was a change between SQLS 2005 and SQL S2008; the 2005 version of BoL (http://msdn.microsoft.com/en-us/library/ms175003(v=SQL.90).aspx) says that the result of ROUND has the same type as the expression to be rounded, as in your explanation. The 2008 version (http://msdn.microsoft.com/en-us/library/ms175003(v=SQL.100).aspx) and the current version (http://msdn.microsoft.com/en-us/library/ms175003.aspx) both say that for neumerics and decimals the result type is decimal with precision 38. So the explanation you gave was apparently correct for SQL 2005, and incorrect for SQL 2008.
I haven't been able to check the 2008 behavious as I don't yet have SQL on the machine I have with me and am having difficulty getting hold of SQL server at a sensible price. My usual UK suppliers are quoting more for shipping a disc here than the total for licence, disc, and shipping if I were in the UK, so the total price ends up about 2.5 times what it would have been if I'd fixed it last week before I flew out; so I'm looking for a local supplier and in parallel trying to persuade of of the usual suppliers to ship be airmail instead of using UPS or any of the other overpriced and unreliable delivery companies that are so much in vogue in the computing industry; of course if I were in the US I could just buy online and download, but MS doesn't give me that option here. The people I buy other stuff from [books, audio discs, dvd discs] ship to here cheaply using the international mail system instead of fashionable private delivery companies, so I foolishly expected the same from software vendors. I'll try to remember to run your test code when I get the license sorted and the software installed.
Tom
September 13, 2010 at 2:20 am
Hugo Kornelis (9/12/2010)
On SQL Server 2005, the output of sp_help indicates that the column is typed as numeric(8,6), not numeric(38,6). I cannot check this on SQL Server 2008, as I don't have that version installed on this computer. Maybe you can execute this code on SQL 2008 and report back the results?
On SQL 2008 it is also numeric(8,6)
April 22, 2011 at 12:33 pm
So... in the following code
declare @result decimal(12,8);
set @result = round (5/3.0, 4,3);
select @result;
I get 1.66660000
The operation (5/3.0) completes and the rounding is done according to the variable declared
(decimal(12,8) - 1.66666667 - and then truncated to 4 places - 1.6666?
Sorry to be a pain, but how is it that I get 1.66660000 instead of 1.66670000? Which step decides if I get the 7 or 6 at the end?:unsure:
April 25, 2011 at 10:08 am
terrykzncs (4/22/2011)
So... in the following code
declare @result decimal(12,8);
set @result = round (5/3.0, 4,3);
select @result;
I get 1.66660000
The operation (5/3.0) completes and the rounding is done according to the variable declared
(decimal(12,8) - 1.66666667 - and then truncated to 4 places - 1.6666?
Sorry to be a pain, but how is it that I get 1.66660000 instead of 1.66670000? Which step decides if I get the 7 or 6 at the end?:unsure:
It is because of the extra ",3" in your ROUND call.
set @result = round (5/3.0, 4,3);
The third parameter tells round to truncate instead of round if it is non-zero.
Try this and I think you will get what you are looking for.:
declare @result decimal(12,8);
set @result = round (5/3.0, 4);
select @result;
June 1, 2011 at 4:31 am
nice question but setting arithabort on gives completely diff solution
March 7, 2012 at 2:04 pm
Nice question and resources on the rounding. Thanks.
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply