Post reply

Integer datatypes in SQlserver

  • October 14, 2008 at 1:37 pm

    #131716

    Hi all, I am trying to look up on the size differences between int, bigint and small int. I mostly work with Oracle and i am trying to compare the width of a column between Oracle and SQlserver on how much width the column would need.

    Please help. Thanks.

  • October 14, 2008 at 1:47 pm

    #884862

    Smallint is 2 bytes and stores values between -32768 and 32767

    Int is 4 bytes and stores values betweeen -2,147,483,648 and 2,147,483,647

    bigint is 8 bytes and stores values between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 14, 2008 at 2:05 pm

    #884874

    Thanks. But application people want to know more about the width rather than the storage size. Like in Oracle we say number(38), it can hold 38 digits.

    Thank you

  • October 14, 2008 at 2:26 pm

    #884887

    psangeetha (10/14/2008)

    Thanks. But application people want to know more about the width rather than the storage size. Like in Oracle we say number(38), it can hold 38 digits.

    Look at Gail's reply and count the number of digits in the answers that she gave you. Do not count the first digit. I do not know if Oracle counts the signs place or not.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • October 14, 2008 at 2:26 pm

    #884888

    Ints don't have a max number of digits. They have a minimum and maximum value that they can store. As I gave above.

    It's the numeric and decimal datatypes that have a max number of digits.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 14, 2008 at 2:29 pm

    #884891

    psangeetha (10/14/2008)

    Thanks. But application people want to know more about the width rather than the storage size. Like in Oracle we say number(38), it can hold 38 digits.

    And just in case you do not already know: the equivalent of Number(38) in SQL Server is NUMERIC(38) or DECIMAL(38).

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • October 14, 2008 at 2:35 pm

    #884895

    Ok Thanks. We are just looking for a datatype we can go with the number of digits.. So the numeric datatype goes with number of digits?? I just tried inserting a record and I think its allowing 18 digits. Please correct me if I am wrong.

    If this is correct, we will use number(18) in Oracle.

    Thanks.

  • October 14, 2008 at 2:41 pm

    #884901

    Then you're looking for numeric(18,0)

    What did you try inserting and into what datatype?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 14, 2008 at 2:58 pm

    #884921

    I tried below

    create table test ( a numeric)

    insert into test values (999999999999999999)

    This worked.

    insert into test values (9999999999999999990) didnt work. So I guess the maximum value for numeric datatype is 18

    digits.

    Is there any other datatype in SQL server that works with the number of digits and not the min max value?

    Thanks.

  • October 14, 2008 at 3:15 pm

    #884926

    The thing is, Oracle does not really have binary integer datatypes the way that SQL does. I think that there may be something like PLS_INTEGER which is equivalent to INTEGER in SQL Server, but that is about it.

    This makes for real problems when moving data back and forth. Because the SQL Server Integer sizes are between the decimal sizes that Oracle uses, which size you use depends on whether you are moving data from Oracle to SQL Server or from SQL Server to Oracle. Effectively, the receiver of the data always has to round up the number of digits from the sender.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • October 14, 2008 at 3:22 pm

    #884929

    psangeetha (10/14/2008)

    I tried below

    create table test ( a numeric)

    insert into test values (999999999999999999)

    This worked.

    insert into test values (9999999999999999990) didnt work. So I guess the maximum value for numeric datatype is 18

    digits.

    Uh, no. 18 is the defualt, not the maximum. Try the following SQL code, it should make things clearer:

    create table #test ( a numeric)

    insert into #test values (999999999999999999)

    SELECT * FROM #TEST

    go

    insert into #test values (9999999999999999999)

    SELECT * FROM #TEST

    go

    create table #test2 ( a numeric(38))

    SELECT * FROM #TEST2

    go

    insert into #test2 values (9999999999999999999)

    SELECT * FROM #TEST2

    go

    insert into #test2 values (99999999999999999999999999999999999999)

    SELECT * FROM #TEST2

    go

    insert into #test2 values (999999999999999999999999999999999999999)

    SELECT * FROM #TEST2

    go

    drop table #test

    drop table #test2

    GO

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • October 15, 2008 at 1:03 am

    #885043

    You should always declare the precision and scale of a numeric. If you want something that takes 18 digits and no decimal places, it's numeric (18,0). Don't rely on the defaults.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 15, 2008 at 9:01 am

    #885296

    Ok. THank you very much everyone for your help.

  • October 15, 2008 at 9:29 am

    #885309

    Glad we could help.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

Viewing 14 posts - 1 through 13 (of 13 total)

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