Storing array of integer values in SQL Server Storing array of integer values in SQL Server sql sql

Storing array of integer values in SQL Server

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.

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.

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?