Author |
Topic |
mattshu
Starting Member
1 Post |
Posted - 2010-09-20 : 02:03:23
|
I'm building a program to collect customer information, etc.Currently the phone number will be stored as an 'int' datatype, but I'm worried that 8315551399 (phone number) will be stored as 8 trillion 'bits' instead of a flat, two-dimensional 8315551399.Should I change the datatype to varchar and treat it as a string to save space? |
|
Sachin.Nand
2937 Posts |
Posted - 2010-09-20 : 02:33:02
|
Are you sure you are going to store the phone numbers in unformatted type?Sachin.Nandanwar |
|
|
slimt_slimt
Aged Yak Warrior
746 Posts |
Posted - 2010-09-20 : 11:20:42
|
i don't know about your database / table design and where you are going to get the data.If phone number will be inserted via GUI by application that i would suggest you to restrict phone number to integer (if integer will hold all valid combinations of your possible telephone numbers). you can even create a special column with all known prefixes and another column with all international access codes. breaking down to this design, this way it should work.if you are getting phone numbers directly from your favorite telephone central (CTI, PBX, etc.) i would stick to original telephony design or at least suggest to use varchar field for phone number; since you can not predict 100% in what format phone number will be returned from your local telephone operators. |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2010-09-20 : 11:38:28
|
You'll probably need to use a varchar filed for phone numbers'0131 554 3421'Is a very different phone number from1315543421For exampleAlso you often get things like '+44(0) 131 231 2132'for international dialing codes.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-09-20 : 11:43:06
|
I would recommend varchar for phone numbers, not an int.Down where I live, all phone numbers have a leading 0. Eg: 091 455 23567Store that in an int and what you get back won't be a valid number.Regarding storage space, an int is 4 bytes in size. But the question here isn't size, it's validity of data. Sure, you can save around 10 bytes per row by storing my example in an int not a varchar(14), but the data will be incorrect when retrieved.--Gail ShawSQL Server MVP |
|
|
jcelko
Esteemed SQL Purist
547 Posts |
Posted - 2010-09-20 : 14:17:07
|
Should I change the datatype to varchar and treat it as a string to save space? [/quote]No, you should change the data type to CHAR(n) be able to get valid data. Look up the ITU Standards (http://www.itu.int/oth/T0202.aspx?parent=T0202) and add a "phone_nbr CHAR(15) LIKE '[0-9]..)" constraint to your table. You can also get validation software from http://www.quentinsagerconsulting.com/documents/10025.htm--CELKO--Books in Celko Series for Morgan-Kaufmann PublishingAnalytics and OLAP in SQLData and Databases: Concepts in Practice Data, Measurements and Standards in SQLSQL for SmartiesSQL Programming Style SQL Puzzles and Answers Thinking in SetsTrees and Hierarchies in SQL |
|
|
X002548
Not Just a Number
15586 Posts |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2010-09-20 : 14:30:41
|
So you can't store phone "numbers" like 800-Vote4me? N 56°04'39.26"E 12°55'05.63" |
|
|
X002548
Not Just a Number
15586 Posts |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2010-09-21 : 03:41:22
|
Sorry Brett. My response was Mr Celko's constraint proposal. N 56°04'39.26"E 12°55'05.63" |
|
|
|