SQL Server创建非聚集索引时指定分区与不指定的差异是什么

核心区别 #

两种写法创建出来的索引结构在你的场景下完全一致，唯一的差异是配置的声明方式：

• 第一种写法（隐式继承分区配置）

CREATE NONCLUSTERED INDEX IX_Table1_City
  ON dbo.table1 (Col1, City)
  INCLUDE (County, Street)

只要底层表已经是按Col1分区的分区表，不指定ON子句时，非聚集索引会默认和表使用相同的分区方案、分区键，自动生成分区对齐的索引。

• 第二种写法（显式声明分区配置）

CREATE NONCLUSTERED INDEX IX_Table1_City
  ON dbo.table1 (Col1, City)
  INCLUDE (County, Street)
ON [Partition_Name] (Col1)

直接指定索引使用[Partition_Name]分区方案、按Col1分区，只要你指定的分区方案和表的分区方案一致，最终生成的索引和第一种没有任何区别。

第二种写法的必要性和适用场景 #

显式指定分区方案的写法并不是冗余设计，它主要用在以下场景：

• 需要创建非对齐分区索引：如果业务需要让非聚集索引和表使用不同的分区键、不同的分区方案，比如表按Col1分区，希望索引按City分区，就必须通过ON子句显式指定对应的分区方案和分区键，这种非对齐索引适合某些特定的查询优化场景。
• 提升可维护性，降低出错概率：如果数据库内存在多个不同的分区方案，或者后续有可能调整表的默认分区配置，显式声明索引的分区规则可以避免索引的分区行为不符合预期，其他维护人员看建索引语句的时候也能直接知道索引的分区规则，不需要额外查表的分区配置。
• 索引独立存储的需求：如果希望把索引数据存到独立的文件组来提升IO性能，也可以通过ON子句指定独立的分区方案或者文件组，实现索引和表数据的物理存储分离。

业务场景选型建议 #

你所有的查询都固定带WHERE Col1 = 1的条件，两种写法生成的都是按Col1分区的对齐索引，查询时都会直接定位到Col1=1对应的分区，不会扫描其他分区，性能没有任何差异。如果没有特殊的存储或者分区规则需求，两种写法都可以使用，显式写法的可维护性略高。

内容的提问来源于stack exchange，提问作者JJ.