Best choice to store a list of ints in mssql

Question

I am wondering which method is the best way to store a list of integers in a sql column. .....i.e. "1,2,3,4,6,7"

EDIT: These values represent other IDs in SQL tables. The row would look like

[1] [2]

id, listOfOtherIDs

The choices I have researched so far are:

1. A varchar of separated value that are "explode-able" i.e. by commas or tabs
2. An XML containing all the values individually
3. Using individual rows for each value.

Which method is the best method to use?

Thanks, Ian

Answer 1

While Jeroen's answer is valid for "multi-valued" attributes, there are genuine situations where multiple comma-separated values may actually be representing one large value. Things like path data (on a map), integer sequence, list of prime factors and many more could well be stored in a comma-separated varchar. I think it is better to explain what exactly are you storing and how do you need to retrieve and use that value.

EDIT:

Looking at your edit, if by IDs you mean PK of another table, then this sounds like a genuine M-N relation between this table and the one whose IDs you're storing. This stuff should really be stored in a separate gerund, which BTW is a table that would have the PK of each of these tables as FKs, thus linking the related rows of both tables. So Jeroen's answer very well suits your situation.

Answer 2

A single element of a record can only refer to one value; it's a basic database design principle.

You will have to change the database's design: use a single row for each value. You might want to read up on normalization.

As is shown here in the description of the first normal form:

First normal form states that at every row and column intersection in the table there, exists a single value, and never a list of values. For example, you cannot have a field named Price in which you place more than one Price. If you think of each intersection of rows and columns as a cell, each cell can hold only one value.