Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2005
    Location
    Sharjah, UAE
    Posts
    25

    Unanswered: Identity column.

    Kudos to y'all! On most of my tables, I'm using a column of data type numeric and properties set as Identity with no replication. Now I know that it can handle only a length of 9 and obviously not nullable. Now by length of 9, does it mean in bytes or literal digits. If it is, then that would mean that it would reach only up to 999,999,999. What happens then if it reaches that digit?
    Pinoy ako, pinoy tayo! Ipagmalaki mo...

  2. #2
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by trojanz
    Kudos to y'all! On most of my tables, I'm using a column of data type numeric and properties set as Identity with no replication. Now I know that it can handle only a length of 9 and obviously not nullable. Now by length of 9, does it mean in bytes or literal digits. If it is, then that would mean that it would reach only up to 999,999,999. What happens then if it reaches that digit?
    Numeric datatype ...
    decimal and numeric
    Numeric data types with fixed precision and scale.

    decimal[(p[, s])] and numeric[(p[, s])]

    Fixed precision and scale numbers. When maximum precision is used, valid values are from - 10^38 +1 through 10^38 - 1. The SQL-92 synonyms for decimal are dec and dec(p, s).

    p (precision)

    Specifies the maximum total number of decimal digits that can be stored, both to the left and to the right of the decimal point. The precision must be a value from 1 through the maximum precision. The maximum precision is 38. The default precision is 18.

    s (scale)

    Specifies the maximum number of decimal digits that can be stored to the right of the decimal point. Scale must be a value from 0 through p. Scale can be specified only if precision is specified. The default scale is 0; therefore, 0 <= s <= p. Maximum storage sizes vary, based on the precision.

    --microsoft

    You can see its larger than what you think.
    You can use uniqueidentifier (A globally unique identifier (GUID)) in this case where you can be sure that the above problem will never arise for time being.
    But I think after the year 9999 ,those data can have duplicate values.

    uniqueidentifier
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if you use INTEGER, you can store up to 2 billion (which is a bit bigger than 999,999,999)

    if that's not big enough, use BIGINT
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Aug 2005
    Location
    Sharjah, UAE
    Posts
    25
    Quote Originally Posted by r937
    if you use INTEGER, you can store up to 2 billion (which is a bit bigger than 999,999,999)

    if that's not big enough, use BIGINT
    Thanks for the tip r937. I tried your suggestion but it turns out that with INT data type and set as identity column, the length is only 4 whilst the BIGINT data type gives me only 8. So that would mean...

    NUMERIC-> 999999999
    INT-> 9999
    BIGINT-> 99999999

    ...or am I lookin' at it the wrong way?
    Pinoy ako, pinoy tayo! Ipagmalaki mo...

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, you are looking at it the wrong way

    INT stores numbers from -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647)

    BIGINT stores numbers from -2^63 (-9,223,372,036,854,775,808) through 2^63-1 (9,223,372,036,854,775,807)

    look it up in Books OnLine

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Apr 2006
    Location
    System.Web.UI.WebControls.Home.BedRoom2
    Posts
    42

    Lightbulb Wake Up

    This is how the math is done on computers:

    Int type is 4 bytes, which is 4*8=32 bits, which means 2^31~= 2Gigs(10^9) unique numbers
    (I think a bit is take off for +/-)

    BigInt type is 8 bytes, which is 8*8=64 bits, which means 2^63 ~= 8*10^18 unique numbers.

    have fun

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    In short, the length of the datatype is the number of bytes it requires. Character data is 1 byte per character (2 for unicode), so the length of the datatype equals the length of the string. Numeric datatypes are stored in binary, as Spere explains above.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Aug 2005
    Location
    Sharjah, UAE
    Posts
    25
    Thanks guys for the explanation. So the length doesn't necessary mean the number of digits/characters but the number of bytes. Interesting. I'll look it up for some more on BOL. Thanks again guys!
    Pinoy ako, pinoy tayo! Ipagmalaki mo...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •