If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Microsoft SQL Server > long numbers in SQL server

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Jan 2003
Location: The Netherlands, Oudkarspel
Posts: 27
Re: long numbers in SQL server

THNX
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
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!"
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
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!"
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
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!)
Reply With Quote
  #9 (permalink)  
Old
Registered User
 
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!)
Reply With Quote
  #10 (permalink)  
Old
Registered User
 
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.

Quote:
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
Reply With Quote
  #11 (permalink)  
Old
Registered User
 
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!"
Reply With Quote
  #12 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #13 (permalink)  
Old
Registered User
 
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 !
Reply With Quote
  #14 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #15 (permalink)  
Old
Registered User
 
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!"
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On