CREATE COLUMNSTORE INDEX (Transact-SQL)

在 SQL Server 表上创建非聚集内存中列存储索引。 使用非聚集列存储索引可利用列存储压缩来显著改进针对只读数据的查询执行时间。

若要创建聚集列存储索引，请参阅 CREATE CLUSTERED COLUMNSTORE INDEX (Transact-SQL)。

有关详细信息，请参阅以下主题：

• 列存储索引简介

• 使用非聚集列存储索引

• 使用聚集列存储索引

Transact-SQL 语法约定

语法

Create a non-clustered columnstore index.
CREATE [ NONCLUSTERED ] COLUMNSTORE INDEX index_name 
    ON [database_name. [schema_name ] . | schema_name . ] table_name  
        ( column  [ ,...n ] )
    [ WITH ( <columnstore_index_option> [ ,...n ] ) ]
    [ ON {
        partition_scheme_name ( column_name ) 
        | filegroup_name 
        | "default" 
        } 
    ]
[ ; ]

<columnstore_index_option> ::=
{
      DROP_EXISTING = { ON | OFF }
    | MAXDOP = max_degree_of_parallelism
 }

参数

• index_name
  指定索引的名称。 index_name 在表中必须唯一，但在数据库中不必唯一。 索引名称必须符合标识符的规则。

  对于非聚集列存储索引，

• ( column [ ,...n ] )
  指定要存储的列。 非聚集列存储索引限定为 1024 个列。

  每个列都必须采用列存储索引支持的数据类型。 有关支持的数据类型列表，请参阅限制和局限。

• ON [database_name。[schema_name ] . | schema_name . ] table_name
  指定将包含该索引的由一部分、两部分或三部分名称组成的表。

• ON
  这些选项指定将创建该索引的文件组。

  • partition_scheme_name ( column_name )
    指定分区方案，该方案定义要将分区索引的分区映射到的文件组。 必须通过执行 CREATE PARTITION SCHEME 使数据库中存在该分区方案。 column_name 指定对已分区索引进行分区所依据的列。 该列必须与 partition_scheme_name 使用的分区函数参数的数据类型、长度和精度相匹配。 column_name 不限于索引定义中的列。 在对列存储索引进行分区时，如果尚未指定分区依据列，则数据库引擎会添加分区依据列作为索引列。

    如果未指定 partition_scheme_name 或 filegroup 且该表已分区，则索引会与基础表使用相同分区依据列并被放入同一分区方案中。

    分区表的列存储索引必须实现分区对齐。

    有关将索引分区的详细信息，请参阅已分区表和已分区索引。

  • filegroup_name
    指定要对其创建索引的文件组名称。 如果未指定 filegroup_name 并且表未分区，则索引将与基础表使用相同的文件组。 该文件组必须已存在。

  • "default"
    为默认文件组创建指定索引。

    在此上下文中，“default”一词不是关键字。 它是默认文件组的标识符，并且必须进行分隔（类似于 ON "default" 或 ON [default]）。 如果指定了 "default"，则当前会话的 QUOTED_IDENTIFIER 选项必须为 ON。 这是默认设置。 有关详细信息，请参阅 SET QUOTED_IDENTIFIER (Transact-SQL)。

  • DROP_EXISTING
    指定删除并重新生成已命名的先前存在的索引。 默认值为 OFF。

    • ON
      删除并重新生成现有索引。 指定的索引名称必须与当前的现有索引相同；但可以修改索引定义。 例如，可以指定不同的列或索引选项。
    • OFF
      如果指定的索引名称已存在，则会显示一条错误。 使用 DROP_EXISTING 不能更改索引类型。 在向后兼容的语法中，WITH DROP_EXISTING 等效于 WITH DROP_EXISTING = ON。

  • MAXDOP = max_degree_of_parallelism
    只在索引操作期间覆盖 配置 max degree of parallelism 服务器配置选项 配置选项。 使用 MAXDOP 可以限制在执行并行计划的过程中使用的处理器数量。 最大数量为 64 个处理器。

    max_degree_of_parallelism 可为以下值：

    • 1 - 取消生成并行计划。

    • >1 - 根据当前系统工作负荷，将并行索引操作中使用的最大处理器数限制为指定数量或更少。 例如，当，MAXDOP = 4，使用的处理器数将为 4 或更小。

    • 0（默认值） - 根据当前系统工作负荷使用实际的处理器数量或更少数量的处理器。

    有关详细信息，请参阅配置并行索引操作。

    备注

    并非在 Microsoft SQL Server 的每个版本中均支持并行索引操作。有关 SQL Server 的每个版本支持的功能列表，请参阅 SQL Server 2014 各个版本支持的功能。

权限

要求对表具有 ALTER 权限。

一般备注

可以为临时表创建列存储索引。 在删除表或结束会话时，也将删除索引。

如果列存储索引不支持您的列的数据类型，则必须从列存储索引中省略该列。

限制和局限

非聚集列存储索引：

• 包含的列数不能超过 1024。

• 具有非聚集列存储索引的表可以具有唯一约束、主键约束或外键约束，但这些约束不能包括在非聚集列存储索引中。

• 不能基于视图或索引视图创建。

• 不能包含稀疏列。

• 不能通过使用 ALTER INDEX 语句更改。 若要更改非聚集索引，必须先删除该列存储索引，然后重新创建它。 您可以使用 ALTER INDEX 禁用和重新生成列存储索引。

• 不能通过使用 INCLUDE 关键字创建。

• 不能包括用来对索引排序的 ASC 或 DESC 关键字。 根据压缩算法对列存储索引排序。 排序将抵销许多性能优势。

列存储索引中的每列必须是以下公共业务类型数据类型之一。

• datetimeoffset [ ( n ) ]

• datetime2 [ ( n ) ]

• datetime

• smalldatetime

• date

• time [ ( n ) ]

• float [ ( n ) ]

• real [ ( n ) ]

• decimal [ ( precision [ , scale ] ) ]

• money

• smallmoney

• bigint

• int

• smallint

• tinyint

• bit

• 不支持 nvarchar [ ( n ) ]，nvarchar (max) 除外

• nchar [ ( n ) ]

• varchar [ ( n ) ]

• char [ ( n ) ]

• 不支持 varbinary [ ( n ) ]，varbinary (max) 除外

• binary [ ( n ) ]

• uniqueidentifier

使用以下任何数据类型的列都不能包括在列存储索引中。

• ntext、text 和 image

• varchar(max) 和 nvarchar(max)

• rowversion（和 timestamp）

• sql_variant

• CLR 类型（hierarchyid 和空间类型）

• xml

• uniqueidentifier

列存储索引不能与以下功能结合使用：

• 页和行压缩以及 vardecimal 存储格式（列存储索引已采用不同格式压缩）。

• 复制

• 更改跟踪

• 变更数据捕获

• 文件流

有关性能优势和列存储索引的局限性的信息，请参阅列存储索引简介。

元数据

列存储索引中的所有列在元数据中作为包含性列存储。 列存储索引中没有任何键列。 这些系统视图提供有关列存储索引的信息。

• sys.indexes (Transact-SQL)

• sys.index_columns (Transact-SQL)

• sys.partitions (Transact-SQL)

• sys.column_store_segments (Transact-SQL)

• sys.column_store_dictionaries (Transact-SQL)

• sys.column_store_row_groups (Transact-SQL)

[返回页首]

示例

A.创建一个简单的非聚集列存储索引

下面的示例创建了一个简单表和聚集索引，然后演示了创建非聚集列存储索引的语法。

CREATE TABLE SimpleTable
(ProductKey [int] NOT NULL, 
OrderDateKey [int] NOT NULL, 
DueDateKey [int] NOT NULL, 
ShipDateKey [int] NOT NULL);
GO
CREATE CLUSTERED INDEX cl_simple ON SimpleTable (ProductKey);
GO
CREATE NONCLUSTERED COLUMNSTORE INDEX csindx_simple
ON SimpleTable
(OrderDateKey, DueDateKey, ShipDateKey);
GO

B.使用所有选项创建简单非聚集索引

下面的示例演示了通过使用所有选项创建非聚集列存储索引的语法。

CREATE NONCLUSTERED COLUMNSTORE INDEX csindx_simple
ON SimpleTable
(OrderDateKey, DueDateKey, ShipDateKey)
WITH (DROP_EXISTING =  ON, 
    MAXDOP = 2)
ON "default"
GO

有关使用分区表的更复杂示例，请参阅列存储索引简介。

更改非聚集列存储索引中的数据

在您在表上创建非聚集列存储索引后，不能直接在该表中修改数据。 具有 INSERT、UPDATE、DELETE 或 MERGE 的查询将失败并且返回错误消息。 若要添加或修改表中的数据，可以执行以下操作之一：

• 禁用或删除列存储索引。 然后可以更新表中的数据。 如果禁用列存储索引，则可以在完成数据更新后重新生成列存储索引。 例如，

  ALTER INDEX mycolumnstoreindex ON mytable DISABLE;
  -- update mytable --
  ALTER INDEX mycolumnstoreindex on mytable REBUILD
  

• 将数据加载到不含列存储索引的临时表中。 在临时表上生成列存储索引。 将临时表切换到主表的一个空分区中。

• 将分区从具有列存储索引的表切换到一个空的临时表中。 如果在临时表上有某个列存储索引，则禁用该列存储索引。 执行任何更新。 生成（或重新生成）列存储索引。 将临时表切换回主表的（现在为空的）分区中。

[返回页首]