CODEWITHSUNDEEP
c#sqlsql-server-2005objectdatasource
spoekes
spoekes

Reputation: 833

Storing array of integer values in SQL Server

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 :)

Upvotes: 11

Views: 31351

Answers (4)

RedArcCoder
RedArcCoder

Reputation: 121

I would store it in the coma separated value if the data is NOT RELATED to any other table (example, values you wanna process in some way), if they are (products in a bill, for example) you should create another table with a foreign key.

Greatings

Upvotes: 0

James K. Lowden
James K. Lowden

Reputation: 7837

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.

Upvotes: 5

Aliostad
Aliostad

Reputation: 81670

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?

Upvotes: 0

Mark Byers
Mark Byers

Reputation: 838536

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.

Upvotes: 24

Related Questions