Round And Numaric

  • asifkareem

    Ten Centuries

    Points: 1089

    Comments posted to this topic are about the item Round And Numaric

  • Dineshbabu

    Hall of Fame

    Points: 3220

    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..

  • Toreador

    SSChampion

    Points: 11220

    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 🙂

  • Dineshbabu

    Hall of Fame

    Points: 3220

    Thanks Dude.

    Letz wait for authors reply...

    --
    Dineshbabu
    Desire to learn new things..

  • Raghavendra Mudugal

    SSChampion

    Points: 10658

    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.

  • Toreador

    SSChampion

    Points: 11220

    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

    http://msdn.microsoft.com/en-us/library/ms190476.aspx

  • Hugo Kornelis

    SSC Guru

    Points: 64645

    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.


    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/

  • paul.knibbs

    SSCoach

    Points: 15270

    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!

  • SQLRNNR

    SSC Guru

    Points: 281107

    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

  • TomThomson

    SSC Guru

    Points: 104707

    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

  • SanDroid

    SSChampion

    Points: 10068

    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 😎

  • Koen Verbeeck

    SSC Guru

    Points: 258859

    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 12 (of 12 total)

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