I know storing multiple values in a column. Not a good idea.
我知道在列中存储多个值。不是个好主意。
It violates first normal form --- which states NO multi valued attributes. Normalize period...
它违反了第一种正常形式 - 它表示没有多值属性。规范化期间......
I am using SQL Server 2005
我正在使用SQL Server 2005
I have a table that require to store lower limit and uppper limit for a measurement, think of it as a minimum and maximum speed limit... only problem is only 2 % out of hundread i need upper limit. I will only have data for lower limit.
我有一个表需要存储测量的下限和uppper限制，将其视为最小和最大速度限制...只有2％的问题只是hundread我需要上限。我只会有下限数据。
I was thinking to store both values in a column (Sparse column introduces in 2008 so not for me)
我想将这两个值存储在一个列中（稀疏列在2008年引入，所以不适合我）
Is there a way...? Not sure about XML..
有办法......？不确定XML ..
3 个解决方案
#1
2
You'd have to be storing an insane amount of rows for this to even matter. The price of a 1 terabyte disk is now 60 dollars!
你必须为此存储疯狂的行数。 1TB磁盘的价格现在是60美元！
Two floats use up 8 bytes; an XML string will use a multiple of that just to store one float. So even though XML would store only one instead of two columns, it would still consume more space.
两个浮点数占用8个字节; XML字符串将使用其中的一个来存储一个浮点数。因此，即使XML只存储一列而不是两列，它仍然会消耗更多空间。
Just use a nullable column.
只需使用可以为空的列。
#2
2
To answer your question, you could store it as a string with a particular format that you know how to parse (e.g. "low:high").
要回答您的问题，您可以将其存储为具有您知道如何解析的特定格式的字符串（例如“low：high”）。
But ... this is really not a good idea.
但是......这真的不是一个好主意。
Dealing with 98% of the rows having NULL value for upper limit is totally fine IMHO. Keep it clean, and you won't regret it later.
处理98％的具有NULL值的行为上限是完全正常的恕我直言。保持清洁，以后你不会后悔。
#3
1
Even so, I agree with Andomar. Use two colums, low limit and high limit. If either value could be unknown, make those columns nullable.
即便如此，我也同意Andomar。使用两个柱，下限和上限。如果任何一个值可能未知，请将这些列设为可为空。
Alternatively, designate a default arbitrary minimum and maximum values, and use those values instead of nulls. (Doing this means you never have to mess with trinary logic, e.g. having to wrap everything with ISNULL or COALESCE.)
或者，指定默认的任意最小值和最大值，并使用这些值而不是空值。 （这样做意味着你永远不必弄乱三元逻辑，例如必须用ISNULL或COALESCE包装所有内容。）
Once you define your schema, there are tricks you can use to reduce storage space (such as compression and sparce columns).
定义架构后，可以使用一些技巧来减少存储空间（例如压缩和sparce列）。
#1
2
You'd have to be storing an insane amount of rows for this to even matter. The price of a 1 terabyte disk is now 60 dollars!
你必须为此存储疯狂的行数。 1TB磁盘的价格现在是60美元！
Two floats use up 8 bytes; an XML string will use a multiple of that just to store one float. So even though XML would store only one instead of two columns, it would still consume more space.
两个浮点数占用8个字节; XML字符串将使用其中的一个来存储一个浮点数。因此，即使XML只存储一列而不是两列，它仍然会消耗更多空间。
Just use a nullable column.
只需使用可以为空的列。
#2
2
To answer your question, you could store it as a string with a particular format that you know how to parse (e.g. "low:high").
要回答您的问题，您可以将其存储为具有您知道如何解析的特定格式的字符串（例如“low：high”）。
But ... this is really not a good idea.
但是......这真的不是一个好主意。
Dealing with 98% of the rows having NULL value for upper limit is totally fine IMHO. Keep it clean, and you won't regret it later.
处理98％的具有NULL值的行为上限是完全正常的恕我直言。保持清洁，以后你不会后悔。
#3
1
Even so, I agree with Andomar. Use two colums, low limit and high limit. If either value could be unknown, make those columns nullable.
即便如此，我也同意Andomar。使用两个柱，下限和上限。如果任何一个值可能未知，请将这些列设为可为空。
Alternatively, designate a default arbitrary minimum and maximum values, and use those values instead of nulls. (Doing this means you never have to mess with trinary logic, e.g. having to wrap everything with ISNULL or COALESCE.)
或者，指定默认的任意最小值和最大值，并使用这些值而不是空值。 （这样做意味着你永远不必弄乱三元逻辑，例如必须用ISNULL或COALESCE包装所有内容。）
Once you define your schema, there are tricks you can use to reduce storage space (such as compression and sparce columns).
定义架构后，可以使用一些技巧来减少存储空间（例如压缩和sparce列）。