Storing array of integer values in SQL Server

Question

Hi, i want to store an array of integer values in a SQL database table (SQLServer 2005), if possible by using a single column.

The integer array will have a length of 7560 values.

I am using a objectdatasource, the datatype should be compatible with the generated parameters of a tableadapter.

thanks for helping :)

Answer 1

You have at least two choices:

• Store it as a comma separated list of values.
• Use a separate table and store one value per row, with a foreign key pointing back to your table.

If you want to normalize your database you should take the second option.

Answer 2

Only if you have to! You can easily create another table which contains foreign key back to your table and an int column.

If you insist on keeping it in SQL Server as a column, you have to use IMAGE column type or VARBINARY(MAX) since your data length exceeds 8K. This will store each int as a 4 byte binary value.

What is ObjectDataSource?

Answer 3

use comma seperated values, but I feel it would make it heavy.......or why not create a seperate master detail table.....

Sorry if this is a dump solution....

Answer 4

Do it right: 1NF stipulates no repeating values. Each element in your proposed 7560-element array belongs in its own row.

By putting each element in its own row, you give the RDBMS a chance to do things it can't do otherwise, e.g.: compute statistics on the set, verify each element adheres to domain rules, compute differences between two sets, count/select sets sharing some characteristics.

i will end up with millions of rows (perhaps more than 50 million). I am not sure if the database can handle that without performance problems.

That's not particularly many, and you won't need to deal with all 50 million most of the time. Calculate for yourself how many accesses are needed to search a binary tree to find one record in a billion. The answer may surprise you.