Store array of numbers in database field

sql-server database datastore data-storage
THX-1138 picture THX-1138 · Mar 17, 2011 · Viewed 49k times · Source

Context: SQL Server 2008, C#

I have an array of integers (0-10 elements). Data doesn't change often, but is retrieved often.

I could create a separate table to store the numbers, but for some reason it feels like that wouldn't be optimal.

Question #1: Should I store my array in a separate table? Please give reasons for one way or the other.

Question #2: (regardless of what the answer to Q#1 is), what's the "best" way to store int[] in database field? XML? JSON? CSV?

EDIT: Some background: numbers being stored are just some coefficients that don't participate in any relationship, and are always used as an array (i.e. never a value is being retrieved or used in isolation).

Answer

gbn picture gbn · Mar 17, 2011

Separate table, normalized

Not as XML or json , but separate numbers in separate rows

No matter what you think, it's the best way. You can thank me later

Related questions

Reset identity seed after deleting records in SQL Server

I have inserted records into a SQL Server database table. The table had a primary key defined and the auto increment identity seed is set to “Yes”. This is done primarily because in SQL Azure, each table has to have …

Read More
How do I list all the columns in a table?

For the various popular database systems, how do you list all the columns in a table?

Read More
How to replace a string in a SQL Server Table Column

I have a table (SQL Sever) which references paths (UNC or otherwise), but now the path is going to change. In the path column, I have many records and I need to change just a portion of the path, but …

Read More