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?

JoachimUse 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|||THNX|||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...|||...oh...and decimal would also do the trick...|||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|||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.|||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.|||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!|||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|||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.|||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|||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 !|||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.|||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).|||Who wants to use this post thread to speak more about aproximations and good practices how to prevent them here?
I am interested.|||Originally posted by ispaleny
Who wants to use this post thread to speak more about aproximations and good practices how to prevent them here?
I am interested.

No problem.

Joachim|||I would be happy to contribute my .0200 cents worth but I am more interested in knowing if JReckers got his question answered.

Maybe a new thread would be better?|||Originally posted by Paul Young
I would be happy to contribute my .0200 cents worth but I am more interested in knowing if JReckers got his question answered.

Maybe a new thread would be better?

I have my questiong for a big part answered Paul Young. A have one question left which I would like to be answered.

The question is:
I thought that the 'length' option in SQL Server was to set the amount of digits (and with a char datatype the amount of characters) that can be used to store the data in, but it seems to be the amount of bytes where the data is stored in. Please correct me if I'm wrong.

Now my question is why Microsoft did this? I've worked with other databases before (Sybase and MySQL) and in these databases it is possible to set the length of the field (the maxium amount of characters the data can use). At least I always thought it was like this.

Joachim|||Microsoft & Sybase are exactly the same on this, there is no difference.

When you specify length you are stateing the amount of digits (and with a char datatype the amount of characters) that can be store and also the amount of bytes used to store the data, they are directly related.

I think in terms of a range when I specify length, for char datatypes I can store Null or 0 to n characters, for an integer, the range is fixed, for a decimal I cna adjust the range to sute my needs and not take up more space than needed to store my data.

Are we just talking past each other?|||Originally posted by Paul Young
Microsoft & Sybase are exactly the same on this, there is no difference.

When you specify length you are stateing the amount of digits (and with a char datatype the amount of characters) that can be store and also the amount of bytes used to store the data, they are directly related.

I think in terms of a range when I specify length, for char datatypes I can store Null or 0 to n characters, for an integer, the range is fixed, for a decimal I cna adjust the range to sute my needs and not take up more space than needed to store my data.

Are we just talking past each other?

;) Yes I think so.

Thanx anyway. You have been a great help. I think I got it now.

Joachim|||Maybe my first reply on this subject was a little too ... unpolite. If it was I'm sorry. Then I got angry, but now I think it's time to stop all this fight, 'cause it's going nowhere. I'm sorry.

I want to explain why I've got so angry about money datatype:

One year ago I was working for Academy of Economic Studies in Bucharest, were I was dba and programer for a database which deals with the activity of students(grades, collecting taxes and so on). One day a huge problem arrised: one secretary told me that the report oin which were listed the averages of grades for students is wrong, because one average is listed like 9.19 and not 9.20 (and from here a lot of problems). I had spent two days in front of my computer to figure out what was the problem. As you allready suspect, in the table were the grades was stored, the field Nota (means grade in romanian) was declared real.

Because of that real the result was 9.19999999... (truncated from the 2-nd decimal, that is the reglementation in Romania for averages of grades)=9.19, instead of 9.20.

The fun part was that if the the average was for instance 9.30 then the result was good because the long number format was 9.3000000000001 so... I hate datatypes

So, I hope you understand why I don't want to here about real or flot datatype.

IONUT

PS

This problem seems to arrise also in MS Access. DataTypes name there is Double for float, Single for Real and Currency for money.