Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Jan 2003
    Location
    The Netherlands, Oudkarspel
    Posts
    27

    long numbers in SQL server

    Hi,

    I'm designing a table in SQL server and I'm trying to create a column for a number that is 10 characters long. The only datatype that comes near the 10 characters is a BIGINT wich is 8 characters long. But this is still to short.
    Is it possible to store a number of 10 characters or is the only way to store this number to store it as a char???

    Joachim

  2. #2
    Join Date
    Mar 2003
    Location
    Bucharest, Romania
    Posts
    80

    Re: long numbers in SQL server

    Use money data type, especially if you are going to make calculations with it. It's the only numeric datatype that really works, and suports very large numbers.


    IONUT

  3. #3
    Join Date
    Jan 2003
    Location
    The Netherlands, Oudkarspel
    Posts
    27

    Re: long numbers in SQL server

    THNX

  4. #4
    Join Date
    Jan 2003
    Location
    London, England
    Posts
    106
    That's not really true... the datatype "float" definetly has the largest number support and supports numbers with 309 (!!!) digits. Acording to BOL float is "...a floating-point number from -1.79E+308 to 1.79E+308" ...which is rather large...
    Frettmaestro
    "Real programmers don't document, if it was hard to write it should be hard to understand!"

  5. #5
    Join Date
    Jan 2003
    Location
    London, England
    Posts
    106
    ...oh...and decimal would also do the trick...
    Frettmaestro
    "Real programmers don't document, if it was hard to write it should be hard to understand!"

  6. #6
    Join Date
    Nov 2002
    Posts
    9

    Re: long numbers in SQL server

    What do you mean by 10 characters? The BIGINT datatype is 8 bytes long, which should give it a range from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807, which is 19 digits. A more efficient use would be Decimal (10,0), which I think uses about 5 bytes.

    I don't have access to BOL at this site to verify this, but look in there for datatypes. Numerics use fewer bytes than strings to hold numbers.

    Richard

  7. #7
    Join Date
    Mar 2003
    Location
    Bucharest, Romania
    Posts
    80

    Re: long numbers in SQL server

    I don't want to be rude, but Frettmaestro, I didn't said that money accepts the largest number values, I only said that if you want to make calculation with that field value, then the only solution is money datatype, not real and not float. Try this one in Query Analiser:


    declare @val float
    set @val=920
    select @val,(@val/100) as result


    SURPRISE result=9.19999999999999993. It really works doesn't it?

    Ha Ha Ha

    IONUT

    PS
    You can be a member, not a junior like me or JRECKERS, but please when you post something here try it first to be damm' sure about it.

  8. #8
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    JReckers, when we talk about numbers and the best datatype to store those numbers we talk in terms of integer and real values not number of characters.

    If you have integer values then we further talk about range, when you have real values we talk about range but also precision and scale.

    If your numbers range from -9,999,999,999 to 9,999,999,999 yoor only choice is to store that number in a bigint data type who's range is -2^63 (-9,223,372,036,854,775,808) through 2^63-1 (9,223,372,036,854,775,807). The Integer data type only covers -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647). Of course I am assuming that you WANT to store the data as a number rather than a string of numbers (varchar).

    As for real numbers you are looking at a decimal or a float data type. What is the diffrence? A decimal is fixed presision and scale where as a float is an approximation, not all values in the data type range can be precisely represented BUT as the name implies the decimal point can float.
    Paul Young
    (Knowledge is power! Get some!)

  9. #9
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    ionut calin, you may want to head your own words! A money data type isn't the only data type that will yield a non-apporiximation!
    Paul Young
    (Knowledge is power! Get some!)

  10. #10
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249

    Re: long numbers in SQL server

    declare @val money
    set @val=920
    select @val,(@val/1000000*1000000) as result
    GO
    declare @val float
    set @val=920
    select @val,(@val/1000000*1000000) as result

    Free advice is seldom cheap.

    Originally posted by ionut calin
    I don't want to be rude, but Frettmaestro ...
    declare @val float
    set @val=920
    select @val,(@val/100) as result


    SURPRISE result=9.19999999999999993. It really works doesn't it?

    Ha Ha Ha

    IONUT

  11. #11
    Join Date
    Jan 2003
    Location
    London, England
    Posts
    106
    Wow ispaleny, that last one was really cool. Did you try it ionut? You said that "It's the only numeric datatype that really works" and I wasn't trying to mock you or anything, just telling you that this wasn't the case. No need to be all cocky and "all that" even if I didn't provide the right answer... I'm just trying to help people here and if you don't appreciate that then that's your problem. And I _really_ could care less what your or mine or anybody elses member status is.
    Frettmaestro
    "Real programmers don't document, if it was hard to write it should be hard to understand!"

  12. #12
    Join Date
    Jan 2003
    Location
    The Netherlands, Oudkarspel
    Posts
    27

    RE:

    So, if I am correct, you are saying that when I create a table with a column with the datatype INT that the length of this datatype is the number of bytes that is used to store the number in and not the amount of characters.

    So, when I want to store a number of 10 digits (min. 0 and max. 9.999.999.999), I have to use a BIGINT, because an INT goes up to 2,147,483,647 which is nog enough.

    Am I correct?

    Joachim

  13. #13
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    Code:
    DataType | Bytes | Digits 
    ---------------------------
    int                4     9(10)
    numeric(9)    5     9
    bigint            8    18(19) 
    numeric(10)   9    10
    char(9)          9      9
    char(10)       10    10
    nchar(9)       18     9
    nchar(10)     20    10
    If you really need 10 digits, with bigint you get +13% performance in comparison with numeric(10)
    and at least +25% in comparison with char(10). Compared with 9 digits, the performance is -50% !!!!!!!

    Good luck !

  14. #14
    Join Date
    Mar 2003
    Location
    Bucharest, Romania
    Posts
    80

    Re: long numbers in SQL server

    For the original question, if you only want to store the number (integer) and not ever make calculations based on that number, the bigint is a good solution.

    Now, because I see there are very upset persons around here:

    I had tried it, and I've also tried this one:


    declare @val as money
    set @val=920
    select @val,(@val*1000000/1000000) as result

    It works isn't it??.


    The float datatype sucks (or real for that matter), because for :

    declare @val as float
    set @val=920
    select @val,(@val/100) as result

    you really have no workaround, to get a correct result.

    The money datatype is designed to work with four decimal numbers, and in this range it works correctly. The float datatype may be bigger, but it doesn't work correctly even with small numbers.

    The last example with @Val/1000000*1000000 works in float datatype only because the errors are leveled. So the example is good only like a "joke" nothing more. I advise everyone to try with @val declared as float:


    @val/1000
    @val/10000
    ...
    @val*0.001
    @val*0.0001

    and now try:

    @val/10000000 -> Surprise it works, why??? No one knows............


    All numbers above are within the four digits range of money datatype(more than enough, for banks for example)


    So, Frettmaestro don't be so happy because for,the so called "errors" with money datatype there is a logical explanation, but for float???

    For the flot datatype I can't find one. Maybe you will find one and share with us, will you?

    IONUT


    PS!
    Now I really wanted to be rude.

  15. #15
    Join Date
    Jan 2003
    Location
    London, England
    Posts
    106
    Why do you _want_ to be rude? As I said way up there I didn't try to mock you with my initial post and I'm still not trying (or wanting) to be rude. You have to admint that I wasn't wrong when I said that "It's the only numeric datatype that really works" isn't really true, but recomending float probably wasn't the best solution in this case. I recomended using decimal in my second post which you haven't taken into account at all. I'm positive that money will do the trick in this case as with bigint, decimal, numeric and actually float aswell (the real issue had nothing to do with dividing any numbers, just storing it).
    Frettmaestro
    "Real programmers don't document, if it was hard to write it should be hard to understand!"

Posting Permissions

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