11

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

AdaTheDev
  • 142,592
  • 28
  • 206
  • 200
spoekes
  • 833
  • 3
  • 8
  • 14
  • why not just make a big text field and separate the values by comma? or use a blob as bitmap (4 bytes per int), or alternatively create a table with 7561 fields :P – joni Oct 27 '10 at 12:16

4 Answers4

23

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.

Mark Byers
  • 811,555
  • 193
  • 1,581
  • 1,452
  • 1
    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 Oct 27 '10 at 13:43
  • 2
    @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. – Mark Byers Oct 27 '10 at 17:49
  • 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 Oct 28 '10 at 08:41
  • 1
    After some testing i go with option 1. Thanks for your help! – spoekes Oct 28 '10 at 13:31
5

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.

James K. Lowden
  • 7,574
  • 1
  • 16
  • 31
  • 1
    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. – windmaomao Jan 16 '16 at 13:58
0

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

RedArcCoder
  • 101
  • 8
0

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?

Aliostad
  • 80,612
  • 21
  • 160
  • 208
  • 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. – spoekes Oct 27 '10 at 13:46
  • Peformance of VARBINARY(MAX) or IMAGE fields are not so good although is fine enough. – Aliostad Oct 27 '10 at 14:04