假设我们有一个包含三列的大型表，如下所示：

[id] INT NOT NULL,

[date] SMALLDATETIME NOT NULL,

[sales] FLOAT NULL

还要假设我们仅限于一个物理磁盘和一个文件组(PRIMARY)。我们希望此表在100多个日期(很容易是1B +记录)中保持10,000,000+ id的销售额。

与许多数据仓库场景一样，数据通常会按日期顺序增长(即，每次执行数据加载时，我们将插入新日期，并且可能会更新一些最近的数据日期)。出于分析目的，通常会查询和汇总约10,000个ID的随机集合的数据，这些ID将通过与另一个表的联接来指定。通常，这些查询没有指定日期范围，也没有指定非常宽的日期范围，这使我想到了一个问题：对表进行索引/分区的最佳方法是什么？

我已经考虑了一段时间，但是陷入了相互矛盾的解决方案中：

选项＃1：由于数据将按日期顺序加载，因此将聚簇索引(和主键)定义为[date]，[id]。还要在日期上创建一个"滑动窗口"分区功能/方案，以允许新数据快速移入/移出表。潜在创建ID的非聚集索引与查询的帮助。

预期结果1：此设置将非常快地用于数据加载，但在最坏的情况下(不受日期限制，很不幸需要查询ID集)，在分析读取方面次优。可以读取数据页。

选项＃2：由于一次只查询一小部分id的数据，因此将聚簇索引(和主键)定义为[id]，[date]。不要费心创建一个分区表。

预期结果2：由于无法再按日期快速限制，因此在加载数据时预期会严重影响性能。对于我的分析查询，预期可带来巨大的性能收益，因为它将最大程度地减少读取的数据页数。

选项＃3：聚类(和主键)，如下所示：[id]，[date]； "滑动窗口"分区功能/方案的日期。

预期结果3：不确定会发生什么。鉴于聚簇索引的第一列是[id]，因此(据我所知)数据是按ID排列的，我希望我的解析查询具有良好的性能。但是，数据是按日期划分的，这与聚簇索引的定义相反(但仍然对齐，因为日期是索引的一部分)。我没有找到太多有关这种情况的文档，以及从中可以获得的性能好处(如果有的话)，这使我想到了最后的奖励问题：

如果我要在一个磁盘上的一个文件组上创建一个表，并且在一列上具有聚集索引，那么从同一列上定义分区有什么好处(除了加载数据时的分区切换)？

解决方案

如果在select语句中使用分区，则cn会有所提高。

如果我们不使用它，而仅使用"标准"选择，那么我们将没有任何好处。

关于原始问题：我建议我们选择选项1，其中包含ID上的非聚集索引。

在本地化I / O时，聚集索引将为我们提供查询的性能优势。日期是一种传统的分区策略，因为许多D / W查询都按日期查看移动。

根据分区表的经验法则，建议分区的大小应在10m行左右。

从各种分析工作负载上的聚簇索引中获得大量性能提升，这在一定程度上是不寻常的。查询优化器将使用一种称为"索引交集"的技术来选择行，而无需访问事实表。有关我在另一个问题上所做的帖子，请参见此处，它通过一些链接对此进行了更深入的解释。
聚集索引可能会也可能不会参与索引交集，因此我们可能会发现它在一般查询工作负载上的收益相对较小。

我们可能会在加载时发现一些情况，其中聚集索引会给我们带来一些好处，特别是如果我们已经获得了在ETL流程中计算出的计算结果(例如Earned Premium)。在这种情况下，我们可能会获得一些好处。如果我们有一个特定的查询，我们知道它会一直执行，那么为此使用聚簇索引可能很有意义。如果我们希望这种类型的查询占应用程序工作的绝大多数，那么选项2和3只会使我们受益匪浅。

对于灵活的系统，简单的日期范围分区在ID上具有索引(如果分区包含一个范围，则日期可能会获得与其他任何日期一样好的性能。群集索引受限的情况可能会带来一些好处。通过在数据上构建多维数据集并确保针对此查询正确设置了聚合，还可以获得一些好处。

我将执行以下操作：

• [Id]上的非聚集索引
• [日期]上的聚集索引
• 将[sales]数据类型转换为数字而不是float

这张桌子真窄。如果实际表会这么窄，那么我们应该很高兴进行表扫描，而不是使用index->​​ lookups。

我会这样做：

CREATE TABLE Narrow
(
  [id] INT NOT NULL,
  [date] SMALLDATETIME NOT NULL,
  [sales] FLOAT NULL,
  PRIMARY KEY(id, date)  --EDIT, just noticed your id is not unique.
)

CREATE INDEX CoveringNarrow ON Narrow(date, id, sales)

这可以处理带有查找的点查询和针对日期条件和id条件的有限扫描的范围广泛的查询。没有从索引中按记录查找。是的，我将写入时间(和使用的空间)增加了一倍，但是，imo。

如果需要特定的数据(并且通过分析证明了这一需求！)，我将针对该表的该部分创建一个集群视图。

CREATE VIEW Narrow200801
AS
SELECT * FROM Narrow WHERE '2008-01-01' <= [date] AND [date] < '2008-02-01'
--There is some command that I don't have at my finger tips to make this a clustered view.

可以按名称在查询中使用群集视图，或者在FROM和WHERE子句合适时，优化器将选择使用群集视图。例如，此查询将使用群集视图。请注意，查询中引用了基表。

SELECT SUM(sales) FROM Narrow WHERE '2008-01-01' <= [date] AND [date] < '2008-02-01'

索引使我们可以方便地访问特定的列...聚类视图使我们可以方便地访问特定的行。

按日期对表进行分区。几个水平分区的性能要比具有这么多行的一张大表的性能更好。

如果插入的插入速度比3.33 ms的日期时间分辨率快，则date列上的聚集索引不好。
如果这样做，我们将获得两个具有相同值的键，并且索引将必须获得另一个内部唯一化器，这将增加其大小。

我会选择＃2.