Geek Answers Handbook

Storing an array of integer values ​​in SQL Server

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

An integer array will have a length of 7560.

I am using objectdatasource, the data type must be compatible with the generated tableadapter parameters.

thanks for the help:)

+7
c # sql sql-server-2005 objectdatasource
source share
4 answers

You have at least two options:

  • Save it as a comma separated list of values.
  • Use a separate table and keep one value for each row, and the foreign key points to your table.

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

+15
source share

Do it right: 1NF does not require duplicate values. Each element in the proposed array of 7560 elements belongs to its own row.

By putting each element on its own line, you give RDBMS the ability to do things that it cannot do otherwise, for example: calculate statistics on a set, verify that each element adheres to domain rules, calculate differences between two sets, count / select sets general characteristics .

i will end in millions of lines (possibly more than 50 million). I'm not sure if the database can handle this without performance issues.

This is not particularly much, and you do not have to deal with all 50 million in most cases. Calculate for yourself how much access is required to search the binary tree to find one entry in a billion. The answer may surprise you.

+5
source share

Only if you need to! . You can easily create another table containing the foreign key into the table and int column.

If you insist on storing it in SQL Server as a column, you should use the IMAGE or VARBINARY (MAX) column type, as your data is longer than 8K. This will store each int as a 4 byte binary value.

What is an ObjectDataSource?

0
source share

I would save it in a value divided by comas, if the data is NOT RELATED to any other table (for example, the values ​​you want to process in any way), if there are any (for example, goods in the invoice), you should create another table with a foreign key.

CONGRATULATIONS

0
source share

More articles:


All Articles