how should we declare long numbers

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

how should we declare long numbers

Author

Topic

rtutus
Aged Yak Warrior

522 Posts

Posted - 2006-10-21 : 16:55:54

when dealing with long numbers in appllications like: 1000000000 up to 9999999999999
in what type is it better to declare them (varchar, bigint.....)
Thanks

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-10-21 : 17:37:18

bigint

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

robvolk
Most Valuable Yak

15732 Posts

Posted - 2006-10-21 : 18:26:19

It would help to know what these numbers are meant for. For instance, if they are just codes without mathematical function (like zip codes or phone numbers) then varchar is fine. However, if they are meant to hold large numbers for calculations, you should also consider the decimal/numeric type, especially if you need any decimal precision. A number of SQL Server functions only work on ints or return floats, and you may lose accuracy in calculations that use them with decimal types.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-10-21 : 18:28:46

Never store numbers as varchar.

CODO ERGO SUM

rtutus
Aged Yak Warrior

522 Posts

Posted - 2006-10-28 : 09:49:52

for example phone numbers and account numbers : 198887773334 and 5142040097 that i don t think we ll need to make math calculations on
Thank you

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-10-28 : 09:54:20

bigint

If you're sure that they'll always be numbers

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2006-10-28 : 10:20:27

quote:
Originally posted by spirit1

bigint

If you're sure that they'll always be numbers

you mean (signed) 64bit integers, right?

after all 10^66 is a *number*, but it won't fit in a bigint column.

SqlSpec - a fast, cheap, and comprehensive data dictionary generator for
SQL Server 2000/2005 and Analysis Server 2005 - http://www.elsasoft.org

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2006-10-28 : 10:24:15

quote:
Originally posted by rtutus

for example phone numbers and account numbers : 198887773334 and 5142040097 that i don t think we ll need to make math calculations on
Thank you

You should not use varchar for such numbers - requires more space. you need a byte for each char in a varchar, plus a couple more to hold the length (14 in your example).

a bigint is only 8 bytes.

varchar should only be used if there are non-numeric chars in your acct numbers, or if you ints are wider than 64bit.

SqlSpec - a fast, cheap, and comprehensive data dictionary generator for
SQL Server 2000/2005 and Analysis Server 2005 - http://www.elsasoft.org

robvolk
Most Valuable Yak

15732 Posts

Posted - 2006-10-28 : 14:15:39

quote:
varchar should only be used if there are non-numeric chars in your acct numbers, or if you ints are wider than 64bit

Or if you are storing CODES with significant leading zeros, such as zip codes. These are NOT NUMBERS, they simply happen to contain nothing but digits. It's not safe to assume an int will suffice for 5 or 9 digits and simply padding the left with zeros will format it correctly. Nor can you apply a rule to an int that can validate such values. I suppose a smallmoney could be hacked to provide 5+4 zip codes, but it's definitely a hack and will rely on external code to interpret. And the fact is that these codes do not require mathematical operations, hence no reason to be interpreted as a number.

International phone numbers, while numeric, also have formats that may or may not need to be preserved. Certainly a flat string of digits is difficult for a user to interpret. And having a setting of some kind to flag which country the number belongs to involves either additional storage, or external logic to properly interpret a code.

quote:
You should not use varchar for such numbers - requires more space. you need a byte for each char in a varchar, plus a couple more to hold the length (14 in your example)

Space considerations are secondary to proper storage, IMHO. For instance, do you store IP addresses as text or as 4 binary bytes?

Kristen
Test

22859 Posts

Posted - 2006-10-28 : 14:24:28

+1 for what Rob said. Saved me typing a much less eloquent reply!

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2006-10-28 : 14:38:24

agreed, true enough.

my perspective is clouded by the number crunching i've been doing lately...

SqlSpec - a fast, cheap, and comprehensive data dictionary generator for
SQL Server 2000/2005 and Analysis Server 2005 - http://www.elsasoft.org