Round up or down III

Question

Post reply

Round up or down III

Hugo Kornelis

SSC Guru

Points: 64780
More actions
August 16, 2010 at 9:48 pm

#226164

Comments posted to this topic are about the item Round up or down III
Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Viewing 15 posts - 1 through 15 (of 28 total)

You must be logged in to reply to this topic. Login to reply

UMG Developer SSChampion Points: 13482 More actions · Answer 1

Nice question, thanks! I do find it interesting that they combined rounding and truncating in one function...

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 2

Nice question, thanks. A good follow up of the previous rounding questions.

And a great explanation.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Christian Buettner-167247 SSChampion Points: 13729 More actions · Answer 3

Christian Buettner-167247

SSChampion

Points: 13729

August 17, 2010 at 12:26 am

#1207673

Very good as usual:)

Best Regards,

Chris Büttner

Yuvaraj S-467280 Old Hand Points: 358 More actions · Answer 4

interesting...

__________________________________________________________
Yuvaraj

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 5

Good question Hugo, learned something new.

---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

Tom Garth SSCertifiable Points: 6193 More actions · Answer 6

Good one. Made me look it up.

Thanks.

Tom Garth
Vertical Solutions[/url]

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers

Ron McCullough SSC Guru Points: 63877 More actions · Answer 7

For a completly different answer - execute the code with the setting for

ARITHABORT ON and note the difference. An important fact to be aware of.

Added after reading Hugo Kornelis Posted Today @ 9:14 AM posting

Copied and tested his code and got his answers. Unfortunately I did not save my test code and now can not duplicate my results, and I tested not less than 5 times ......... darn if I know the difference ..

SO ALL PLEASE DISREGARD MY COMMENTS ABOUT GETTING A DIFFERENT ANSWER WITH ARITHABORT SETTING!

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 8

Great question, thanks. If I keep seeing order of precedence questions on QoTD, I will eventually have it memorized, and won't need to look it up any more.

Hugo Kornelis SSC Guru Points: 64780 More actions · Answer 9

bitbucket-25253 (8/17/2010)
For a completly different answer - execute the code with the setting for
ARITHABORT ON and note the difference. An important fact to be aware of.

Huh? I executed this code:

SET ARITHABORT ON;

DECLARE @Result decimal(5,2);

SET @Result = ROUND(5/3.0, 1, 2);

PRINT @Result;

go

SET ARITHABORT OFF;

DECLARE @Result decimal(5,2);

SET @Result = ROUND(5/3.0, 1, 2);

PRINT @Result;

And I got 1.60 as both results. What did you get?

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Toreador SSChampion Points: 11358 More actions · Answer 10

Interesting.

Can anyone explain exactly what the 3rd argument of Round() is for?

The BOL says

"function Is the type of operation to perform. function must be tinyint, smallint, or int. When function is omitted or has a value of 0 (default), numeric_expression is rounded. When a value other than 0 is specified, numeric_expression is truncated."

which in practice seems to mean that 0 means round, any other value means truncate. So

SET @Result = ROUND(5/3.0, 1, 2);

SET @Result = ROUND(5/3.0, 1, 1);

SET @Result = ROUND(5/3.0, 1, 999999999);

SET @Result = ROUND(5/3.0, 1, -12345.6789);

all do the same thing.

So why not just make it a Bit datatype and call it 'Truncate' instead of 'Function'? Did it original behave differently?

SanjayAttray SSChampion Points: 13157 More actions · Answer 11

Interesting question and good explanation.

For a normal person 3 and 3.0 is same. programming can be manipulative.

SQL DBA.

Ron McCullough SSC Guru Points: 63877 More actions · Answer 12

Hugo Kornelis

Copied and tested your code and got your answers. Unfortunately I did not save my test code and now can not duplicate my results, and I tested that not less than 5 times ......... darn if I know the difference ..

My apologies to all ...... will do some further investigation to see if I can discover what I did incorrectly.

Again sorry - - I did edit my post so as not to pass on what is now not correct information.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Hugo Kornelis SSC Guru Points: 64780 More actions · Answer 13

Toreador (8/17/2010)
So why not just make it a Bit datatype and call it 'Truncate' instead of 'Function'? Did it original behave differently?

Good question. And I have no idea.

I did check in the SQL Server 2000 version of Books Online (the oldest version I was able to find online), and it was the same then.

Maybe it's for ANSI compliance? (the SQL Server BIT datatype is not ANSI-standard)

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

webrunner SSC-Dedicated Points: 31711 More actions · Answer 14

I went 0 for 3 on the rounding questions. Scary. Well, what was really scary was that I didn't read through the ROUND documentation after the first question, which no doubt would have helped for the next 2 questions I encountered. I will make sure to read through the references more carefully from now on.

They were all great questions, though, and progressed in a way where a new twist was introduced for each one. Nice work.

Thanks,

webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html