Store array in SQL Server 2008

Question

I am developing a contact manager application using SQL Server 2008 (service-based database) . Most contacts have several emails or several phone numbers. So is there a way to store an array as a datatype in SQL Server? Or does anyone have an alternative to this way?

Answer 1

No there isn't an array type in SQL.

You should create a phonenumbers table and store one phone number per row use and use a foreign key (called, for example, person_id) to refer to the person table.

Answer 2

You'll want to create separate tables, with a row per contact number or email address.

CREATE TABLE Contacts (contactId int, name varchar(128), etc, etc
CREATE TABLE ContactEmail (contactId int, emailAddress varchar(128), etc
CREATE TABLE ContactPhone (contactId int, phoneNumber varchar(128), etc

This will allow you to modify individual numbers/emails, remove them, add them, etc, without requiring an external program to unpack an array.

But if you really want to store it denormalized, you could transform the array into a delimited string. . put a delimiter between each email address (with the appropriate magic to make sure an address doesn't already contain the delimiter) then split it on the way back out.

Answer 3

One other option you can use is to store the contact information as an XML document in a XML type field in your table. I would only do this if I am not trying to search for people based on some part of that contact information.

For example if you are think you might be interested in finding all your contacts who have phone numbers with a specific area code then you will want to make that a separate table and relate it instead of storing it this way.

I know that you can actually query the XML in SQL Server 2008, but it really depends on how many records you are going to have in your contact list as parsing all that XML on a lot of data will be very slow compared to having separate tables.