Data Types

Question

Post reply

Data Types

da.drew

SSC Rookie

Points: 37
More actions
November 25, 2010 at 1:46 am

#227193

Can somebody please advise me on the best Data Types to use for various Coloums. Im adding the ollowing Coloums to my database:
Post Code
Telephone Number
Mobile Number
Email
Thanks

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Brigadur SSCrazy Points: 2097 More actions · Answer 1

Hi,

My recommendations:

Postcode

If you always have same length then use char(<length>). If it can have different lengths like international postcodes, safest is nvarchar(<length>).

Telephone Number, Mobile Number

These can indeed be tricky:

1. One column for country code, one column for area code and one column for the phone number

2. Store the whole as varchar(<length>)

The requirements should help in deciding which alternative to choose.

There are some standard ways to store telephone numbers that handles international numbers, like Microsoft Outlook does, it is good practice to check the input and convert to a uniform format.

Email

Similar choices as for telephone number, either store as whole string, or split it up into the components, like domain, user, etc. Depends on the requirements.

Good practice here also to do at least some basic checkof the format.

Good luck

da.drew SSC Rookie Points: 37 More actions · Answer 2

Hi thanks for your reply, that all makes good sense. What would you suggest for Dates, I read on a website that SQL Server has:

datetime - From January 1, 1753 to December 31, 9999 with an accuracy of 3.33 milliseconds 8 bytes

datetime2 - From January 1, 0001 to December 31, 9999 with an accuracy of 100 nanoseconds 6-8 bytes

smalldatetime - From January 1, 1900 to June 6, 2079 with an accuracy of 1 minute 4 bytes

date - Store a date only. From January 1, 0001 to December 31, 9999 3 bytes

time - Store a time only to an accuracy of 100 nanoseconds 3-5 bytes

datetimeoffset - The same as datetime2 with the addition of a time zone offset 8-10 bytes

timestamp

But I can only find, small datetime, datetime and time stamp!

Nakul Vachhrajani SSChampion Points: 10294 More actions · Answer 3

da.drew (11/25/2010)
Hi thanks for your reply, that all makes good sense. What would you suggest for Dates, I read on a website that SQL Server has:
datetime - From January 1, 1753 to December 31, 9999 with an accuracy of 3.33 milliseconds 8 bytes
datetime2 - From January 1, 0001 to December 31, 9999 with an accuracy of 100 nanoseconds 6-8 bytes
smalldatetime - From January 1, 1900 to June 6, 2079 with an accuracy of 1 minute 4 bytes
date - Store a date only. From January 1, 0001 to December 31, 9999 3 bytes
time - Store a time only to an accuracy of 100 nanoseconds 3-5 bytes
datetimeoffset - The same as datetime2 with the addition of a time zone offset 8-10 bytes
timestamp
But I can only find, small datetime, datetime and time stamp!

Looks like you are using SQL 2005, the DATE/TIME and DATETIME2 are new to SQL 2008.

Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com

Follow me on
Twitter: @sqltwins

Brigadur SSCrazy Points: 2097 More actions · Answer 4

Yes, it looks like you are using SQL Server 2005, so you are stuck with either datetime and smalldatetime. Which one to choose depends on the range and precision required. Timestamp is not a datetime in SQL Server (at least not yet) as explained here: timestamp

So timestamp has a different purpose.

Cheers

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 5

Brigadur (11/25/2010)
Hi,
My recommendations:
Postcode
If you always have same length then use char(<length>). If it can have different lengths like international postcodes, safest is nvarchar(<length>).
Telephone Number, Mobile Number
These can indeed be tricky:
1. One column for country code, one column for area code and one column for the phone number
2. Store the whole as varchar(<length>)
The requirements should help in deciding which alternative to choose.
There are some standard ways to store telephone numbers that handles international numbers, like Microsoft Outlook does, it is good practice to check the input and convert to a uniform format.
Email
Similar choices as for telephone number, either store as whole string, or split it up into the components, like domain, user, etc. Depends on the requirements.
Good practice here also to do at least some basic checkof the format.
Good luck

I absolutely agree with the splits on telephone number. Keep in mind that many international numbers have a CITY code, as well. Then, there are countries (Mexico, for sure) that also have zone numbers.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 6

Nakul Vachhrajani (11/25/2010)
da.drew (11/25/2010)
Hi thanks for your reply, that all makes good sense. What would you suggest for Dates, I read on a website that SQL Server has:
datetime - From January 1, 1753 to December 31, 9999 with an accuracy of 3.33 milliseconds 8 bytes
datetime2 - From January 1, 0001 to December 31, 9999 with an accuracy of 100 nanoseconds 6-8 bytes
smalldatetime - From January 1, 1900 to June 6, 2079 with an accuracy of 1 minute 4 bytes
date - Store a date only. From January 1, 0001 to December 31, 9999 3 bytes
time - Store a time only to an accuracy of 100 nanoseconds 3-5 bytes
datetimeoffset - The same as datetime2 with the addition of a time zone offset 8-10 bytes
timestamp
But I can only find, small datetime, datetime and time stamp!
Looks like you are using SQL 2005, the DATE/TIME and DATETIME2 are new to SQL 2008.

Of course, "It Depends", but I normally recommend not splitting date and time for most things. You usually end up just having to put then back together.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

VSSGeorge SSCrazy Eights Points: 8151 More actions · Answer 7

My opinion abt this problem is

1) Postcode : nvarchar datatype will do the purpose

2) Mobile/Phone No: Go for numeric datatype , if it is having only numeric data.

3) Email: Go for varchar itself.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 8

Junglee_George (11/30/2010)
My opinion abt this problem is
1) Postcode : nvarchar datatype will do the purpose
2) Mobile/Phone No: Go for numeric datatype , if it is having only numeric data.
3) Email: Go for varchar itself.

I agree but just so folks don't make a mistake... the reason why it's ok to use something like BIGINT for a whole phone number is because phone numbers aren't supposed to have leading zeros. Don't ever use a numeric datatype for something like a Zip code or a Social Security Number even if they contain only numeric digits because both of those (and many other things) contain leading zeros which will be lost because numeric datatypes don't preserve leading zeros.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)