将列数据存储为行MS SQL的最佳方式

本文关键字：SQL 最佳 方式 MS 数据 存储 | 更新日期: 2023-09-27 18:24:44

我有一些列是动态的数据，列数据的数量可以随时增加/减少。所以我计划将它们按行存储，而不是按列存储。

我放置了列的主表，它指示列使用的数据类型。我正在绘制下面的主表，供您参考

CID     Name          Type
1      Speed         Double
2      Input1        Bool
3      Message       String
.......
.......

现在我想到了两种方法来存储这个动态列数据第一种方法是

CID      Data_bool       Data_String      Data_Double
1        NULL            NULL             12
2        True            NULL             NULL
3        NULL            test             NULL
1        NULL            NULL             5
1        NULL            NULL             15

第二种方法是有一个通用的varchar列，并将每个值作为字符串存储在那里，这样看起来就像

CID      Datas
1        12
2        True
3        test
1        5
1        15

如果从数据库规范化的角度来看，那么第二种方法似乎是不错的。但我认为这会给数据检索带来问题。因为我想过滤像"速度>10"这样的数据。因此，如果我采用第二种方法（将所有值存储为字符串），我认为表达式将需要更多的时间来求值如果我对表达式采取第一种方法，那么首先我需要确定需要对表达式求值的列。例如，对于表达式Speed>10，首先我必须检查Speed是哪种数据类型（字符串、布尔等），然后再次执行"data_double>10"的表达式

两者都有各自的缺点。有人能指出哪种方式将来会让我不那么头疼吗。请记住，在后期阶段，此表将增加数百万条记录。

我很欣赏你在这里的观点和时间。非常感谢。

将列数据存储为行MS SQL的最佳方式

我不在乎你是如何访问数据的，也许SQL_Variant可能是你与SQL_Variant_PROPERTY结合使用的一个选项。

Declare @a table(id int, cont sql_variant)
insert into @a select 1,'test'
insert into @a select 1,Cast('20130101' as DateTime)
insert into @a select 1,Cast('20130201' as Datetime)
insert into @a select 1,Cast(1 as Bit)
insert into @a select 1,Cast(0 as Bit)
Select * from
(
Select *  from @a
where SQL_VARIANT_PROPERTY(cont,'BaseType')='datetime'
) x
Where cont>Cast('20130101' as DateTime)

一种方法可以为您感兴趣的每种数据类型使用一个表。每个表只有两个字段。int类型PK和用于存储数据的相应类型列。在主表中，您可以只拥有一个int类型的FK，它链接到一个特定的类型表，以及另一个tinyint类型的字段，它决定FK属于哪个子表。

主表

ID int PK

ValueID int不为空

类型tinyint不为空

子表

ID int PK

值字符串不为空

从子表到主表的ValueID为FK。可以为其他类型创建类似的子表。

我知道这并不能回答你关于这两个选项中哪一个更好的问题，但我希望它无论如何都会有用。

我不会选择这两个选项中的任何一个。我宁愿试着看看我是否可以将这些列放入列（具有50或100甚至更多列的表并不罕见）和/或不同的表中。

我建议您安装TFS或Dynamics CRM，看看它们是如何存储数据的。他们构建了应用程序代码，以便能够在数据库中添加/删除列，并且他们有一组表来跟踪这些元数据。

如果真的有很多不同的值，我会尝试使用XML数据类型。

我曾多次看到并处理过这种类型的问题，尤其是在应用程序必须允许用户配置字段名和数据类型的情况下。

这些情况下的解决方案是键值（即2列）表，它对所有键[显然]使用varchars，但也对所有值使用varchars。

这是一个非常强大的解决方案，掩盖了它的简单性！

尽管这是最简单和可扩展的选项，但它可能不是最具性能的选项。为每种数据类型提供一个键值表可能会有所帮助，但编程起来有点困难。或者，在同一个表中为每个数据类型包含一个"类型"字段和列（但这不是我的最爱，因为这会浪费空间）。

我开发的基于数据库的应用程序使用varchar-Value方法，执行起来没有明显的缓慢；然而，它们实际上只使用简单的基于键的查找进行操作。您的情况可能会有所不同，尤其是当您对数据进行更复杂的查询时。说明了显而易见的，但是，将主键应用于Key字段将提高查找速度。

附加说明：

很抱歉回收我在各种论坛上读到的内容，但我没有在自己的数据库中使用变体类型。我读过：

1） 在SQLServer2005以后的版本中，使用变量类型而不是varchar类型（在本例中，对于Value列）将导致更快的操作，

2） 它们不适用于WHERE子句中的LIKE，

3） OLE DB和ODBC提供程序会自动将变体转换为nvarchar（4000）。

相关文章：