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
 Best DataType for this situation?

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

Go to Top of Page

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.
Go to Top of Page

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 from

1315543421

For example

Also you often get things like '+44(0) 131 231 2132'

for international dialing codes.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

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 23567
Store 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 Shaw
SQL Server MVP
Go to Top of Page

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 Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2010-09-20 : 14:26:00
For me a simple rule of thumb is....

If you are not going to use math on a column...it's varchar/char



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx





Go to Top of Page

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"
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2010-09-20 : 14:35:06
Or Swe-deg-irls



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx





Go to Top of Page

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"
Go to Top of Page
   

- Advertisement -