i want to store an array of integer values in a SQL database table (SQLServer 2005), if possible by using a single column.
我想在SQL数据库表(SQLServer 2005)中存储一个整数值数组,如果可能的话,使用单个列。
The integer array will have a length of 7560 values.
整数数组的长度为7560。
I am using a objectdatasource, the datatype should be compatible with the generated parameters of a tableadapter.
我使用的是objectdatasource,数据类型应该与tableadapter生成的参数兼容。
thanks for helping :)
谢谢你的帮助:)
4 个解决方案
#1
16
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.
如果要标准化数据库,则应采用第二种方法。
#2
5
Do it right: 1NF stipulates no repeating values. Each element in your proposed 7560-element array belongs in its own row.
做得对:1NF规定没有重复值。建议的7560元素数组中的每个元素都属于自己的行。
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.
通过将每个元素放在自己的行中,您可以让RDBMS有机会做其他事情无法做到的事情,例如:计算集合上的统计数据,验证每个元素是否符合域规则,计算两个集合之间的差异,计数/选择集共享一些特征。
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.
我将以数百万行(可能超过5000万行)结束。我不确定数据库是否可以处理没有性能问题。
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.
这并不是特别多,你不需要在大多数时间内处理所有5000万。自己计算搜索二叉树以找到十亿分之一的记录需要多少次访问。答案可能会让你大吃一惊。
#3
0
Only if you have to! You can easily create another table which contains foreign key back to your table and an int column.
只有你必须!您可以轻松地创建另一个表,其中包含返回表的外键和一个int列。
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.
如果您坚持将其作为列保留在SQL Server中,则必须使用IMAGE列类型或VARBINARY(MAX),因为数据长度超过8K。这将把每个int存储为4字节的二进制值。
What is ObjectDataSource?
什么是ObjectDataSource?
#4
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
#1
16
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.
如果要标准化数据库,则应采用第二种方法。
#2
5
Do it right: 1NF stipulates no repeating values. Each element in your proposed 7560-element array belongs in its own row.
做得对:1NF规定没有重复值。建议的7560元素数组中的每个元素都属于自己的行。
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.
通过将每个元素放在自己的行中,您可以让RDBMS有机会做其他事情无法做到的事情,例如:计算集合上的统计数据,验证每个元素是否符合域规则,计算两个集合之间的差异,计数/选择集共享一些特征。
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.
我将以数百万行(可能超过5000万行)结束。我不确定数据库是否可以处理没有性能问题。
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.
这并不是特别多,你不需要在大多数时间内处理所有5000万。自己计算搜索二叉树以找到十亿分之一的记录需要多少次访问。答案可能会让你大吃一惊。
#3
0
Only if you have to! You can easily create another table which contains foreign key back to your table and an int column.
只有你必须!您可以轻松地创建另一个表,其中包含返回表的外键和一个int列。
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.
如果您坚持将其作为列保留在SQL Server中,则必须使用IMAGE列类型或VARBINARY(MAX),因为数据长度超过8K。这将把每个int存储为4字节的二进制值。
What is ObjectDataSource?
什么是ObjectDataSource?
#4
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