SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Best DataType for this situation?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mattshu
Starting Member

1 Posts

Posted - 09/20/2010 :  02:03:23  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

2937 Posts

Posted - 09/20/2010 :  02:33:02  Show Profile  Reply with Quote
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

Switzerland
746 Posts

Posted - 09/20/2010 :  11:20:42  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 09/20/2010 :  11:38:28  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 09/20/2010 :  11:43:06  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
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

USA
547 Posts

Posted - 09/20/2010 :  14:17:07  Show Profile  Visit jcelko's Homepage  Reply with Quote

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 - 09/20/2010 :  14:26:00  Show Profile  Reply with Quote
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

Sweden
29910 Posts

Posted - 09/20/2010 :  14:30:41  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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 - 09/20/2010 :  14:35:06  Show Profile  Reply with Quote
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

Sweden
29910 Posts

Posted - 09/21/2010 :  03:41:22  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000