indexing - 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!!

Answer 1

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.