January 1, 2013 at 10:23 pm
Comments posted to this topic are about the item Round And Numaric
January 2, 2013 at 2:31 am
In first statement both values are rounded to 0 precision then how come on dividing it returns 6 digits after decimal point.
Pls xplain .
--
Dineshbabu
Desire to learn new things..
January 2, 2013 at 3:08 am
dineshbabus (1/2/2013)
In first statement both values are rounded to 0 precision then how come on dividing it returns 6 digits after decimal point.Pls xplain .
That's the only non-obvious part of the question, and there's mention in the linked reference. I've not found an explanation, but I got it right as I knew the behaviour with float and there was only one answer that matched 🙂
January 2, 2013 at 3:21 am
Thanks Dude.
Letz wait for authors reply...
--
Dineshbabu
Desire to learn new things..
January 2, 2013 at 3:37 am
asifkareem (1/1/2013)
Comments posted to this topic are about the item <A HREF="/questions/T-SQL/95322/">Round And Numaric</A>
it is something like this, the 123 and 100 are integers, but it is converted in to NUMERIC data type with 0 scale, so they are numeric without decimal formats.
now execute the below lines
declare @v1 as numeric(5,0)
declare @v2 as numeric (5,0)
set @v1 = 123
set @v2 = 100
select @v1/@v2
you will see "1.230000" as output, this is happening because of the divide operation on two numeric values which will return with the decimal places becuase
Operation Result precision Result scale [/b]
e1 / e2 p1 - s1 + s2 + max(6, s1 + p2 + 1) max(6, s1 + p2 + 1)
(Checl the "Precision, Scale, and Length (Transact-SQL)" under BOL)
Hope this helps
ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
January 2, 2013 at 3:42 am
Raghavendra Mudugal (1/2/2013)
Hope this helps
It certainly does - and reminds my why I reluctantly stick to float for arithmetic!
Here's the correct reference
January 2, 2013 at 3:50 am
Not very happy with this question.
The first answer option is very clearly wrong - no idea where that 0.12 could possibly come from.
The other two options are the same. Arithmetically, there is no difference between 123, 123.00000, 123.0, or 123.000000000000000000000000000. The only difference is display, and how values are displayed is determined by the client program used, not by SQL Server itself (unless you ask SQL Server to convert to string).
To reply to the question asked above - the only difference between the two is the data type. The value is the same, and the data type is used by the client to govern how the value is displayed.
Query 1 converts two values to numeric(5,0), then divides them. This results in a numeric(11,6) (see http://msdn.microsoft.com/en-us/library/ms190476.aspx). This is multiplied by 100, an integer; this integer is first converted to numeric(3,0) (see http://msdn.microsoft.com/en-us/library/ms190309.aspx), and the result of the multiplication is then numeric(15,6) (same source as for division).
Query 2 converts two values to float, divides them (resulting in float), converts 100 to float and multiplies (again resulting in float), and then invokes the ROUND function, which also returns float. So the end result here is float.
The different answers are then caused by how the client chooses to format the different data types. Using SSMS will indeed result in the answer marked as "correct" (at least on my system and with default settings - this is probably influenced by locale settings, Windows settings, and maybe SSMS settings as well).
When using sqlcmd.exe, I get different results - 123.000000 and 123.0. When creating these two queries as pass-through queries in Access 2010, I get 123 for both queries. I did not try other client programs, but maybe someone else can - ideas to try are a default datagrid in a custom .Net application, Excel, Query Analyzer, or third party tools.
January 2, 2013 at 4:41 am
Hugo Kornelis (1/2/2013)
When using sqlcmd.exe, I get different results - 123.000000 and 123.0.
Odd, I just tested it using SQLCMD and got the answers as listed in the question. Must be something to do with the version of SQLCMD itself, because it did the same connected to either a SQL 2008 R2 or a SQL 2012 instance!
January 2, 2013 at 9:55 am
Nice discussion going on here.
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
January 2, 2013 at 12:00 pm
Rather irritating question, as it stands.
It would be a good question if it specified that the queries were run using SSMS with a default install and no user changes to configuration options. Even then the explanation would be inadequate, because the important stuff is how fixed point numeric arithmetic works and that wasn't mentioned and the explanation neither mentioned that nor provided a reference to the relevant BoL page.
Tom
January 2, 2013 at 12:31 pm
L' Eomot Inversé (1/2/2013)
It would be a good question if it specified that the queries were run using SSMS with a default install and no user changes to configuration options.... and the explanation neither mentioned that nor provided a reference to the relevant BoL page.
+1 😎
January 4, 2013 at 1:29 am
Thanks for the question.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply