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

Question

我的简化人为示例如下：

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

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

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

将所有当前记录和归档记录存储在同一表中。

IE

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％的查询），并且更常见的当前值查询的性能会因为将所有数据放在一个表中而受损，然后将这些数据分离到自己的表中是很有意义的…