What datatype should be used for storing phone numbers in SQL Server 2005?

Question

I need to store phone numbers in a table. Please suggest which datatype should I use? Wait. Please read on before you hit reply.

This field needs to be indexed heavily as the Sales Reps can use this field for searching.

As of now, we are expecting the phone numbers to come in a number of formats (from an XML file). Do I have to write a parser to convert it to a uniform format? There could be millions of data (with duplicates) and I don't want to tie up the server resources (in activities like preprocessing too much) every time some source data comes through.

Any suggestions are welcome.

Answer 1

We use varchar(15) and certainly index on that field.

The reason being is that International standards can support up to 15 digits.

If you want to support International numbers, I recommend the separate storage of a World Zone Code or Country Code to better filter queries by so that you don't find yourself parsing and checking the length of the phone number fields to limit the returned calls to the USA for example.

If you want to learn more about SQL, Check out this SQL Certification by Intellipaat.

For more information visit :

Answer 2

We can use VARCHAR datatype in SQL Server 2005 for storing the phone numbers. You can go with Varchar(15) because you will also be able to store characters that are used as different formatting of numbers like parentheses or hyphens.

A point to note is avoid using numeric data types such as int or bigint for storing phone numbers why because it will restrict you to preserve the formats or identifying leading zeros like 0912345643

Overall VARCHAR data type gives flexibility for managing conserving the formats of numbers.