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 Sales Reps can use this field for searching  including wild character search    As of now  we are expecting phone numbers to come in a number of formats  from an XML file   Do I have to write a parser to convert to a uniform format  There could be millions of data  with duplicates  and I dont want to tie up the server resources  in activities like preprocessing too much  every time some source data comes through    Any suggestions are welcome    Update  I have no control over source data  Just that the structure of xml file is standard  Would like to keep the xml parsing to a minimum  Once it is in database  retrieval should be quick  One crazy suggestion going on around here is that it should even work with Ajax AutoComplete feature  so Sales Reps can see the matching ones immediately   OMG

User · Answer

SQL Server 2005 is pretty well optimized for substring queries for text in indexed varchar fields  For 2005 they introduced new statistics to the string summary for index fields  This helps significantly with full text searching

User · Answer

Since you need to accommodate many different phone number formats  and probably include things like extensions etc   it may make the most sense to just treat it as you would any other varchar  If you could control the input  you could take a number of approaches to make the data more useful  but it doesn t sound that way    Once you decide to simply treat it as any other string  you can focus on overcoming the inevitable issues regarding bad data  mysterious phone number formating and whatever else will pop up  The challenge will be in building a good search strategy for the data and not how you store it in my opinion  It s always a difficult task having to deal with a large pile of data which you had no control over collecting

User · Answer

I would use a varchar 22   Big enough to hold a north american phone number with extension  You would want to strip out all the nasty            -  characters  or just parse them all into one uniform format   Alex

User · Answer

Normalise the data then store as a varchar   Normalising could be tricky   That should be a one-time hit   Then as a new record comes in  you re comparing it to normalised data   Should be very fast

User · Answer

I realize this thread is old  but it s worth mentioning an advantage of storing as a numeric type for formatting purposes  specifically in  NET framework   IE   DefaultCellStyle Format            -         Will not work on a string

User · Answer

Normalise the data then store as a varchar   Normalising could be tricky   That should be a one-time hit   Then as a new record comes in  you re comparing it to normalised data   Should be very fast

User · Answer

I realize this thread is old  but it s worth mentioning an advantage of storing as a numeric type for formatting purposes  specifically in  NET framework   IE   DefaultCellStyle Format            -         Will not work on a string

User · Answer

It is always better to have separate tables for multi valued attributes like phone number   As you have no control on source data so  you can parse the data from XML file and convert it into the proper format so that there will not be any issue with formats of a particular country and store it in a separate table so that indexing and retrieval both will be efficient   Thank you

User · Answer

using varchar is pretty inefficient  use the money type and create a user declared type  phonenumber  out of it  and create a rule to only allow positive numbers   if you declare it as  19 4  you can even store a 4 digit extension and be big enough for international numbers  and only takes 9 bytes of storage  Also  indexes are speedy

User · Answer

I would use a varchar 22   Big enough to hold a north american phone number with extension  You would want to strip out all the nasty            -  characters  or just parse them all into one uniform format   Alex

User · Answer

Does this include    International numbers  Extensions  Other information besides the actual number  like  ask for bobby      If all of these are no  I would use a 10 char field and strip out all non-numeric data  If the first is a yes and the other two are no  I d use two varchar 50  fields  one for the original input and one with all non-numeric data striped and used for indexing  If 2 or 3 are yes  I think I d do two fields and some kind of crazy parser to determine what is extension or other data and deal with it appropriately  Of course you could avoid the 2nd column by doing something with the index where it strips out the extra characters when creating the index  but I d just make a second column and probably do the stripping of characters with a trigger   Update  to address the AJAX issue  it may not be as bad as you think  If this is realistically the main way anything is done to the table  store only the digits in a secondary column as I said  and then make the index for that column the clustered one

User · Answer

Since you need to accommodate many different phone number formats  and probably include things like extensions etc   it may make the most sense to just treat it as you would any other varchar  If you could control the input  you could take a number of approaches to make the data more useful  but it doesn t sound that way    Once you decide to simply treat it as any other string  you can focus on overcoming the inevitable issues regarding bad data  mysterious phone number formating and whatever else will pop up  The challenge will be in building a good search strategy for the data and not how you store it in my opinion  It s always a difficult task having to deal with a large pile of data which you had no control over collecting

User · Answer

Since you need to accommodate many different phone number formats  and probably include things like extensions etc   it may make the most sense to just treat it as you would any other varchar  If you could control the input  you could take a number of approaches to make the data more useful  but it doesn t sound that way    Once you decide to simply treat it as any other string  you can focus on overcoming the inevitable issues regarding bad data  mysterious phone number formating and whatever else will pop up  The challenge will be in building a good search strategy for the data and not how you store it in my opinion  It s always a difficult task having to deal with a large pile of data which you had no control over collecting

User · Answer

nvarchar with preprocessing to standardize them as much as possible  You ll probably want to extract extensions and store them in another field

User · Answer

Use SSIS to extract and process the information  That way you will have the processing of the XML files separated from SQL Server  You can also do the SSIS transformations on a separate server if needed  Store the phone numbers in a standard format using VARCHAR  NVARCHAR would be unnecessary since we are talking about numbers and maybe a couple of other chars  like                    and  -

User · Answer

Use a varchar field with a length restriction

User · Answer

I would use a varchar 22   Big enough to hold a north american phone number with extension  You would want to strip out all the nasty            -  characters  or just parse them all into one uniform format   Alex

User · Answer

We use varchar 15  and certainly index on that field   The reason being is that International standards can support up to 15 digits  Wikipedia - Telephone Number Formats  If you do support International numbers  I recommend the separate storage of a World Zone Code or Country Code to better filter queries by so that you do not find yourself parsing and checking the length of your phone number fields to limit the returned calls to USA for example

User · Answer

We use varchar 15  and certainly index on that field   The reason being is that International standards can support up to 15 digits  Wikipedia - Telephone Number Formats  If you do support International numbers  I recommend the separate storage of a World Zone Code or Country Code to better filter queries by so that you do not find yourself parsing and checking the length of your phone number fields to limit the returned calls to USA for example

User · Answer

Use SSIS to extract and process the information  That way you will have the processing of the XML files separated from SQL Server  You can also do the SSIS transformations on a separate server if needed  Store the phone numbers in a standard format using VARCHAR  NVARCHAR would be unnecessary since we are talking about numbers and maybe a couple of other chars  like                    and  -

User · Answer

It is always better to have separate tables for multi valued attributes like phone number   As you have no control on source data so  you can parse the data from XML file and convert it into the proper format so that there will not be any issue with formats of a particular country and store it in a separate table so that indexing and retrieval both will be efficient   Thank you

User · Answer

Does this include    International numbers  Extensions  Other information besides the actual number  like  ask for bobby      If all of these are no  I would use a 10 char field and strip out all non-numeric data  If the first is a yes and the other two are no  I d use two varchar 50  fields  one for the original input and one with all non-numeric data striped and used for indexing  If 2 or 3 are yes  I think I d do two fields and some kind of crazy parser to determine what is extension or other data and deal with it appropriately  Of course you could avoid the 2nd column by doing something with the index where it strips out the extra characters when creating the index  but I d just make a second column and probably do the stripping of characters with a trigger   Update  to address the AJAX issue  it may not be as bad as you think  If this is realistically the main way anything is done to the table  store only the digits in a secondary column as I said  and then make the index for that column the clustered one

User · Answer

We use varchar 15  and certainly index on that field   The reason being is that International standards can support up to 15 digits  Wikipedia - Telephone Number Formats  If you do support International numbers  I recommend the separate storage of a World Zone Code or Country Code to better filter queries by so that you do not find yourself parsing and checking the length of your phone number fields to limit the returned calls to USA for example

User · Answer

Use a varchar field with a length restriction

User · Answer

Normalise the data then store as a varchar   Normalising could be tricky   That should be a one-time hit   Then as a new record comes in  you re comparing it to normalised data   Should be very fast

User · Answer

SQL Server 2005 is pretty well optimized for substring queries for text in indexed varchar fields  For 2005 they introduced new statistics to the string summary for index fields  This helps significantly with full text searching

User · Answer

Use a varchar field with a length restriction

User · Answer

Use CHAR 10  if you are storing US Phone numbers only  Remove everything but the digits

User · Answer

Does this include    International numbers  Extensions  Other information besides the actual number  like  ask for bobby      If all of these are no  I would use a 10 char field and strip out all non-numeric data  If the first is a yes and the other two are no  I d use two varchar 50  fields  one for the original input and one with all non-numeric data striped and used for indexing  If 2 or 3 are yes  I think I d do two fields and some kind of crazy parser to determine what is extension or other data and deal with it appropriately  Of course you could avoid the 2nd column by doing something with the index where it strips out the extra characters when creating the index  but I d just make a second column and probably do the stripping of characters with a trigger   Update  to address the AJAX issue  it may not be as bad as you think  If this is realistically the main way anything is done to the table  store only the digits in a secondary column as I said  and then make the index for that column the clustered one

User · Answer

SQL Server 2005 is pretty well optimized for substring queries for text in indexed varchar fields  For 2005 they introduced new statistics to the string summary for index fields  This helps significantly with full text searching

User · Answer

nvarchar with preprocessing to standardize them as much as possible  You ll probably want to extract extensions and store them in another field

User · Answer

Use data type long instead   dont use int because it only allows whole numbers between -32 768 and 32 767 but if you use long data type you can insert numbers between -2 147 483 648 and 2 147 483 647

User · Answer

Normalise the data then store as a varchar   Normalising could be tricky   That should be a one-time hit   Then as a new record comes in  you re comparing it to normalised data   Should be very fast

User · Answer

I m probably missing the obvious here  but wouldn t a varchar just long enough for your longest expected phone number work well   If I am missing something obvious  I d love it if someone would point it out

User · Answer

Use SSIS to extract and process the information  That way you will have the processing of the XML files separated from SQL Server  You can also do the SSIS transformations on a separate server if needed  Store the phone numbers in a standard format using VARCHAR  NVARCHAR would be unnecessary since we are talking about numbers and maybe a couple of other chars  like                    and  -

User · Answer

I m probably missing the obvious here  but wouldn t a varchar just long enough for your longest expected phone number work well   If I am missing something obvious  I d love it if someone would point it out

User · Answer

Use CHAR 10  if you are storing US Phone numbers only  Remove everything but the digits

User · Answer

Use data type long instead   dont use int because it only allows whole numbers between -32 768 and 32 767 but if you use long data type you can insert numbers between -2 147 483 648 and 2 147 483 647

User · Answer

We use varchar 15  and certainly index on that field   The reason being is that International standards can support up to 15 digits  Wikipedia - Telephone Number Formats  If you do support International numbers  I recommend the separate storage of a World Zone Code or Country Code to better filter queries by so that you do not find yourself parsing and checking the length of your phone number fields to limit the returned calls to USA for example

User · Answer

Use CHAR 10  if you are storing US Phone numbers only  Remove everything but the digits

User · Answer

Use a varchar field with a length restriction

User · Answer

It is fairly common to use an  x  or  ext  to indicate extensions  so allow 15 characters  for full international support  plus 3  for  ext   plus 4  for the extension itself  giving a total of 22 characters  That should keep you safe   Alternatively  normalise on input so any  ext  gets translated to  x   giving a maximum of 20

User · Answer

It is fairly common to use an  x  or  ext  to indicate extensions  so allow 15 characters  for full international support  plus 3  for  ext   plus 4  for the extension itself  giving a total of 22 characters  That should keep you safe   Alternatively  normalise on input so any  ext  gets translated to  x   giving a maximum of 20

User · Answer

Since you need to accommodate many different phone number formats  and probably include things like extensions etc   it may make the most sense to just treat it as you would any other varchar  If you could control the input  you could take a number of approaches to make the data more useful  but it doesn t sound that way    Once you decide to simply treat it as any other string  you can focus on overcoming the inevitable issues regarding bad data  mysterious phone number formating and whatever else will pop up  The challenge will be in building a good search strategy for the data and not how you store it in my opinion  It s always a difficult task having to deal with a large pile of data which you had no control over collecting

User · Answer

I would use a varchar 22   Big enough to hold a north american phone number with extension  You would want to strip out all the nasty            -  characters  or just parse them all into one uniform format   Alex

User · Answer

Does this include    International numbers  Extensions  Other information besides the actual number  like  ask for bobby      If all of these are no  I would use a 10 char field and strip out all non-numeric data  If the first is a yes and the other two are no  I d use two varchar 50  fields  one for the original input and one with all non-numeric data striped and used for indexing  If 2 or 3 are yes  I think I d do two fields and some kind of crazy parser to determine what is extension or other data and deal with it appropriately  Of course you could avoid the 2nd column by doing something with the index where it strips out the extra characters when creating the index  but I d just make a second column and probably do the stripping of characters with a trigger   Update  to address the AJAX issue  it may not be as bad as you think  If this is realistically the main way anything is done to the table  store only the digits in a secondary column as I said  and then make the index for that column the clustered one

User · Answer

nvarchar with preprocessing to standardize them as much as possible  You ll probably want to extract extensions and store them in another field

User · Answer

nvarchar with preprocessing to standardize them as much as possible  You ll probably want to extract extensions and store them in another field

User · Answer

SQL Server 2005 is pretty well optimized for substring queries for text in indexed varchar fields  For 2005 they introduced new statistics to the string summary for index fields  This helps significantly with full text searching

User · Answer

Use CHAR 10  if you are storing US Phone numbers only  Remove everything but the digits

User · Answer

I m probably missing the obvious here  but wouldn t a varchar just long enough for your longest expected phone number work well   If I am missing something obvious  I d love it if someone would point it out

User · Answer

I m probably missing the obvious here  but wouldn t a varchar just long enough for your longest expected phone number work well   If I am missing something obvious  I d love it if someone would point it out

User · Answer

using varchar is pretty inefficient  use the money type and create a user declared type  phonenumber  out of it  and create a rule to only allow positive numbers   if you declare it as  19 4  you can even store a 4 digit extension and be big enough for international numbers  and only takes 9 bytes of storage  Also  indexes are speedy

User · Answer

Use SSIS to extract and process the information  That way you will have the processing of the XML files separated from SQL Server  You can also do the SSIS transformations on a separate server if needed  Store the phone numbers in a standard format using VARCHAR  NVARCHAR would be unnecessary since we are talking about numbers and maybe a couple of other chars  like                    and  -

[sql-server] What datatype should be used for storing phone numbers in SQL Server 2005?

Examples related to sql-server

Examples related to indexing