Menu
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 :)
849
First, create a table called example with the following SQL statement: CREATE TABLE example ( `id` int NOT NULL AUTO_INCREMENT, `docs` JSON, PRIMARY KEY (`id`) ); The example table will have two columns: the id column and the docs column. And that's the easiest way you can store an array type using MySQL.
You need to use the ARRAY_AGG function to create an array that is the intermediate result of a SELECT statement, and then retrieve the contents of that array into an SQL array variable or parameter. For example: -- INTB IS AN OUT PARAMETER OF ORDINARY ARRAY TYPE INTARRAY. -- COL2 IS AN INTEGER COLUMN.
Conclusion. As you can see, SQL Server does not include arrays. But we can use table variables, temporary tables or the STRING_SPLIT function. However, the STRING_SPLIT function is new and can be used only on SQL Server 2016 or later versions.
You have at least two choices:
If you want to normalize your database you should take the second option.
65
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.
44
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
