Storing array of integer values in SQL Server

Question

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

Solution 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.

Solution 2

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.

Comments

[spoekes]

Thanks for your answer! if i normalize the database 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.

[spoekes]

Thanks for your answer! Are there any drawbacks by using VARBINARY(MAX) or IMAGE? ObjectDataSource is Data Access Layer which generates methods for retrieving data.

[Aliostad]

Peformance of VARBINARY(MAX) or IMAGE fields are not so good although is fine enough.

[Mark Byers]

@spoekes: I think you need to consider your use cases to decide which solution is best for you. Do you always need to fetch and modify the entire list at once? Then using a single cell is probably fine. Will you sometimes be interested in changing just one element or checking if an element is in a list? Then normalizing will most likely give you better performance.

[spoekes]

I need to save the data quick and afterwards load the set of integers and draw a graph with it. There is no need to modify any of those values.

[spoekes]

After some testing i go with option 1. Thanks for your help!

[windmaomao]

there's no need to achieve everything. For tagging, i believe, you should store as array first, and then for performance and report purpose, create another table to facilitate that. This probably is exactly why some people go for mongoDB. But actually it's because people say you should not do this or that which caused it IMHO.