Store array in SQL Server 2008

Question

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

Answer 1

You need to create separate tables with a row for the 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 change individual numbers / emails, delete them, add them, etc., without requiring an external program to unpack the array.

But if you really want to keep it denormalized, you can convert the array to a delimited string., Place a separator between each email address (with the appropriate magic to make sure the address does not yet contain a separator), then split it on the return path.

Answer 2

There is no array type in SQL.

You must create a name table and save one phone number for use in a string and use a foreign key (called, for example, person_id) to refer to the persons table.

Answer 3

Another option you can use is to save the contact information as an XML document in an XML type field in your table. I would only do this if I did not look for people based on some part of this contact information.

For example, if you think that you may be interested in finding all of your contacts that have phone numbers with a specific regional code, then you will want to make this separate table and link it, rather than storing it that way.

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

Answer 4

You can use the table variable to use it, you must use the DDL creation table, but first you need to write at (@). In doing so, you have an array ... but you CANNOT store this on the table, because the table is the array that it has. I really need to have a data structure inside your table, you must use XML. As I understand it, since SQL Server 2000 has several functions for working with XML.

Perhaps if you tell me more details about your reasons for doing this ... I can give you more ideas!

I hope my answer helps you