在SQL Server 2005/2008中存储历史数据的最佳方式是什么？

Question

我的简化和人为的例子如下： -

可以说我想每天测量和存储所有世界城镇的温度（和其他值）。我正在寻找一种存储数据的最佳方式，以便在所有城镇获得当前温度同样容易，因为它可以在一个城镇历史上获得所有温度。

这是一个足够容易解决的问题，但我正在寻找最佳解决方案。是

我能想到的2个主要选项如下： -

选项1 - 相同的表存储当前和历史记录

存储所有当前和存档记录在同一个表。

即

CREATE TABLE [dbo].[WeatherMeasurement](
    MeasurementID [int] Identity(1,1) NOT Null, 
    TownID [int] Not Null, 
    Temp [int] NOT Null, 
    Date [datetime] NOT Null, 
) 

这将让一切简单，但什么是最有效的查询来获取城镇的列表，并有当前的温度？一旦表格中有数百万行，这是否会缩放？通过在表中添加某种IsCurrent标志可以获得什么？

选择2 - 存储在单独的表中的所有档案记录

有将存储在

CREATE TABLE [dbo].[WeatherMeasurement](
    MeasurementID [int] Identity(1,1) NOT Null, 
    TownID [int] Not Null, 
    Temp [int] NOT Null, 
    Date [datetime] NOT Null, 
) 

目前现场的测量数据，并存储历史归档日期表（由插入或许触发）

CREATE TABLE [dbo].[WeatherMeasurementHistory](
    MeasurementID [int] Identity(1,1) NOT Null, 
    TownID [int] Not Null, 
    Temp [int] NOT Null, 
    Date [datetime] NOT Null, 
) 

这有保持主电流数据精简，效率极高查询的优点，在制作模式的详细的费用复杂和插入数据更昂贵。

哪一个是最好的选择？有没有更好的选择，我没有提到？

注意：我简化了模式以帮助更好地集中我的问题，但假设每天都会插入大量数据（100,000条记录），并且数据当前为一天。当前数据与历史数据一样可能被查询。

Answer 1

它取决于应用程序使用模式...如果使用模式指示历史数据将比当前值更频繁地查询，那么将它们全部放在一张表中......但是如果历史查询是例外， （或少于10％的查询），并且更常见的当前值查询的性能会受到将所有数据放在一个表中的影响，那么将该数据分离到它自己的表中是有意义的...

Answer 2

我建议保留在同一张表中，因为历史数据的查询频率很高。除非你会添加更多的列到表中。

当大小成为问题时，您可以将其分区为十年，并使存储过程联合请求的行。

Answer 3

另一种选择可能是为所有数据选择一张表格，并查看当前温度。这不会有助于性能，但可以有助于可读性/可维护性。如果您有适当的sql版本，您甚至可以使用索引视图来提高性能。

Answer 4

我会将数据保存在一张表中除非对当前数据（使用中）或历史数据（数量）有非常严重的偏见。在大多数情况下，具有DATE + TOWNID的复合索引（按该顺序）将消除性能问题（尽管目前我们没有数据确定这一点）。

我想知道的一件事是，如果任何人都需要来自城镇的当前和历史数据的数据。如果是这样，那么您至少创建了一个新视图以便在该方向上担心和可能的性能问题。

这是不幸的事情之一，你可能需要根据真实世界的数据来剖析你的解决方案。我个人在许多情况下都使用了复合索引，例如上面指定的复合索引，然而在我选择将历史记录分解到另一个表格的情况下，还有一些边缘情况。那么，实际上是另一个数据文件，因为问题是历史是，所以密集，我为它创建了一个新的数据文件，以避免膨胀整个主数据文件集。性能问题很少由理论解决。

我建议阅读关于索引使用的查询提示和“覆盖索引”以获取有关性能问题的更多信息。

Answer 5

我将使用带有索引视图的单个表格为我提供最新信息。 SQL 2005和2008服务器专为数据仓库而设计，因此应该在这种情况下完成预制。

如果您有一个需要经常写入数据库的数据模式，那么最好的选择是有一个活动的表和归档表，您可以按某个时间间隔批量更新。

Answer 6

您的表格非常窄，并且可能会在单个正确索引的表中执行，该表不会超出传统标准化OLTP模型中的SQL Server容量，即使对于数百万和数百万行也是如此。即使使用双表模型，可以通过在SQL Server中使用表分区来缓解这些优点。所以它没有太多的推荐它通过单表模型。这将是一个Inmon风格或“企业数据仓库” - 场景。

在更大的场景中，我会定期将数据传输到数据仓库（用Kimball样式的维模型建模），并简单地清除实时数据 - 在像您这样的简单场景中，可能会有效NO实时数据 - 它直接进入仓库。在以不同方式切分数据并存储大量具有各种维度的事实时，维度模型具有许多优点。即使在数据仓库场景中，通常事实表也是按日期分区的。

它看起来可能不是你的数据有这个（城区和日期是你唯一明确的尺寸），但是，在大多数数据仓库，尺寸可以雪花也可以有冗余，所以就有关存储的事实其它尺寸在负载的时候，而不是雪花更高的效率 - 如国家，邮编，WasItRaining，IsStationUrban（人为的）。

这看起来可能很愚蠢，但是当您开始挖掘数据仓库中结果的数据时，这会提出如下问题 - 在城市环境中的一天下雨，缅因州的平均气温是多少？ - 如果不加入大量表格（即，它不需要您的标准化模型的许多专业知识，并且执行速度非常快），就可以轻松获得。有点像棒球中无用的数据 - 但有些显然是有用的。

Answer 7

如果您将所有内容存储在一张表中，您将如何制作关系数据库。

实施例：

ID -------------- GUID ---- PK

RECORD_ID ------- GUID

每次

一个新的记录将被插入[id]会改变，但[record_id]将保持不变。现在，如果你必须把它与地址表连接起来，你打算怎么做？