CREATE COLUMNSTORE INDEX (Transact-SQL)

项目
03/06/2024

适用于：SQL Server Azure SQL 数据库 Azure SQL 托管实例 Azure Synapse Analytics Analytics Platform System (PDW)

将行存储表转换为聚集列存储索引，或创建非聚集列存储索引。使用列存储索引可对 OLTP 工作负载有效地运行实时运营分析，或提高数据仓库工作负载的数据压缩和查询性能。

SQL Server 2022 (16.x) 中引入了有序聚集列存储索引。有关详细信息，请参阅 CREATE COLUMNSTORE INDEX。
从 SQL Server 2016 (13.x) 开始，可以将表创建为聚集列存储索引。再也不需要先创建行存储表，然后将其转换为聚集列存储索引。
有关列存储索引设计指南的信息，请参阅列存储索引 - 设计指南。

语法

SQL Server 和 Azure SQL 数据库的语法：

-- Create a clustered columnstore index on disk-based table.
CREATE CLUSTERED COLUMNSTORE INDEX index_name
    ON { database_name.schema_name.table_name | schema_name.table_name | table_name }
    [ ORDER (column [ , ...n ] ) ]
    [ WITH ( <with_option> [ , ...n ] ) ]
    [ ON <on_option> ]
[ ; ]

-- Create a nonclustered columnstore index on a disk-based table.
CREATE [ NONCLUSTERED ]  COLUMNSTORE INDEX index_name
    ON { database_name.schema_name.table_name | schema_name.table_name | table_name }
        ( column  [ , ...n ] )
    [ WHERE <filter_expression> [ AND <filter_expression> ] ]
    [ WITH ( <with_option> [ , ...n ] ) ]
    [ ON <on_option> ]
[ ; ]

<with_option> ::=
      DROP_EXISTING = { ON | OFF } -- default is OFF
    | MAXDOP = max_degree_of_parallelism
    | ONLINE = { ON | OFF }
    | COMPRESSION_DELAY  = { 0 | delay [ MINUTES ] }
    | DATA_COMPRESSION = { COLUMNSTORE | COLUMNSTORE_ARCHIVE }
      [ ON PARTITIONS ( { partition_number_expression | range } [ , ...n ] ) ]

<on_option>::=
      partition_scheme_name ( column_name )
    | filegroup_name
    | "default"

<filter_expression> ::=
      column_name IN ( constant [ , ...n ]
    | column_name { IS | IS NOT | = | <> | != | > | >= | !> | < | <= | !< } constant )

Azure Synapse Analytics、Parallel Data Warehouse SQL Server 2022 (16.x) 及更高版本的语法：

CREATE CLUSTERED COLUMNSTORE INDEX index_name
    ON { database_name.schema_name.table_name | schema_name.table_name | table_name }
    [ ORDER ( column [ , ...n ] ) ]
    [ WITH ( DROP_EXISTING = { ON | OFF } ) ] -- default is OFF
[;]

注意

若要查看 SQL Server 2014 (12.x) 及更早版本的 Transact-SQL 语法，请参阅早期版本文档。

参数

某些选项并非在所有数据库引擎版本中均可用。下表显示了聚集列存储索引和非聚集列存储索引中引入选项时的版本：

选项	CLUSTERED	NONCLUSTERED
COMPRESSION_DELAY	SQL Server 2016 (13.x)	SQL Server 2016 (13.x)
DATA_COMPRESSION	SQL Server 2016 (13.x)	SQL Server 2016 (13.x)
ONLINE	SQL Server 2019 (15.x)	SQL Server 2017 (14.x)
WHERE 子句	空值	SQL Server 2016 (13.x)

所有选项在 Azure SQL 数据库中均可用。

CREATE CLUSTERED COLUMNSTORE INDEX

创建一个聚集列存储索引，并按列压缩和存储其中的所有数据。该索引包含表中的所有列，并且存储整个表。如果现有表是堆或聚集索引，则会将其转换为聚集列存储索引。如果该表已作为聚集列存储索引存储，则会删除并重新生成现有索引。

index_name

指定新索引的名称。

如果该表已具有聚集列存储索引，则可以指定与现有索引相同的名称，也可以使用 DROP EXISTING 选项指定新名称。

ON [ database_name. [ schema_name ] . | schema_name . ] table_name

指定要作为聚集列存储索引存储的由一部分、两部分或三部分构成的名称。如果该表是堆或具有聚集索引，则会将其从行存储转换为列存储。如果该表已经是列存储，则此语句会重新生成聚集列存储索引。

ORDER

适用于 Azure Synapse Analytics、Analytics Platform System (PDW) 和 SQL Server 2022 (16.x) 及更高版本

column_store_order_ordinal使用sys.index_columns中的列来确定聚集列存储索引的列的顺序。这有助于段消除，尤其是字符串数据。有关详细信息，请参阅使用有序聚集列存储索引和列存储索引进行性能优化 - 设计指南。

若要转换为有序聚集列存储索引，现有索引必须是聚集列存储索引。使用 DROP_EXISTING 选项。

LOB 数据类型（[最大]长度数据类型）不能是有序聚集列存储索引的键。

创建有序聚集列存储索引时，使用 OPTION(MAXDOP = 1) 以通过 CREATE INDEX 语句进行最高质量的排序，以换取 CREATE INDEX 语句的显着更长的持续时间。为了尽可能快地创建索引，不要限制 MAXDOP，并使用服务器所能提供的所有并行线程。最高质量的压缩和排序可以帮助查询列存储索引。

创建有序聚集列存储索引时，键列由 sys.index_columns 中的 column_store_order_ordinal 列指示。

WITH 选项

DROP_EXISTING = [OFF] | ON

DROP_EXISTING = ON 指定删除现有的索引，并创建一个新的列存储索引。

CREATE CLUSTERED COLUMNSTORE INDEX cci ON Sales.OrderLines
WITH (DROP_EXISTING = ON);

DROP_EXISTING = OFF（默认值）要求索引名称与现有名称相同。如果指定的索引名称已存在，则会出错。

MAXDOP = max_degree_of_parallelism

此选项可在索引操作期间覆盖现有的最大并行度服务器配置。使用 MAXDOP 可以限制在执行并行计划的过程中使用的处理器数量。最大数量为 64 个处理器。

max_degree_of_parallelism 可为以下值：

1，这表示取消生成并行计划。
>1，这表示基于当前系统工作负载，将并行索引操作中使用的最大处理器数限制为指定数量或更少。例如，当 MAXDOP = 4 时，使用的处理器数为 4 或更少。
0（默认值），这表示根据当前系统工作负载使用实际的处理器数量或更少数量的处理器。

CREATE CLUSTERED COLUMNSTORE INDEX cci ON Sales.OrderLines
WITH (MAXDOP = 2);

有关详细信息，请参阅配置最大并行度（服务器配置选项）和配置并行索引操作。

COMPRESSION_DELAY = 0 | delay [ MINUTES ]

对于基于磁盘的表，延迟指定增量行组中处于关闭状态的增量行组必须保持为增量行组的最小分钟数。然后 SQL Server 可以将其压缩为压缩行组。由于基于磁盘的表不对单个行跟踪插入和更新时间，因此 SQL Server 会将该延迟应用于处于关闭状态的增量行组。

默认为 0 分钟。

CREATE CLUSTERED COLUMNSTORE INDEX cci ON Sales.OrderLines
WITH ( COMPRESSION_DELAY = 10 MINUTES );

有关何时使用 COMPRESSION_DELAY 的建议，请参阅开始使用列存储进行实时运营分析。

DATA_COMPRESSION = COLUMNSTORE | COLUMNSTORE_ARCHIVE

为指定的表、分区号或分区范围指定数据压缩选项。选项如下：

COLUMNSTORE 是默认值，它指定使用性能最高的列存储压缩进行压缩。此操作是典型选择。
COLUMNSTORE_ARCHIVE 将表或分区进一步压缩为更小的大小。可在许多情况下使用此选项，例如，用于要求存储更小并且可以付出更多时间来进行存储和检索的存档。

CREATE CLUSTERED COLUMNSTORE INDEX cci ON Sales.OrderLines
WITH ( DATA_COMPRESSION = COLUMNSTORE_ARCHIVE );

有关压缩的详细信息，请参阅数据压缩。

ONLINE = [ON | OFF]

ON 指定列存储索引在生成新副本时保持联机并可用。
OFF 指定索引在生成新副本时不可用。

CREATE CLUSTERED COLUMNSTORE INDEX cci ON Sales.OrderLines
WITH ( ONLINE = ON );

ON 选项

使用这些选项，你可为数据存储指定选项，例如分区方案、特定的文件组或默认文件组。如果未指定 ON 选项，索引会使用现有表的分区设置或文件组设置。

partition_scheme_name ( column_name ) 指定表的分区方案。分区方案必须已在数据库中存在。若要创建分区方案，请参阅 CREATE PARTITION SCHEME。

column_name 指定对已分区索引进行分区所依据的列。该列必须与 partition_scheme_name 使用的分区函数参数的数据类型、长度和精度相匹配。

filegroup_name 指定用于存储聚集列存储索引的文件组。如果未指定位置并且表未分区，则索引将与基础表或视图使用相同的文件组。该文件组必须已存在。

若要在默认文件组上创建索引，请使用 "default" 或 [default]。如果指定 "default"，则 QUOTED_IDENTIFIER 选项必须为 ON 当前会话。 QUOTED_IDENTIFIER 默认设置为 ON。有关详细信息，请参阅 SET QUOTED_IDENTIFIER (Transact-SQL)。

CREATE [NONCLUSTERED] COLUMNSTORE INDEX

对存储为堆或聚集索引的行存储表创建非聚集列存储索引。该索引可以具有经过筛选的条件，并且不需要包含基础表的所有列。列存储索引需要足够的空间来存储数据副本。可以更新索引，并在基础表发生更改时对其进行更新。聚集索引上的非聚集列存储索引可启用实时分析。

index_name

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

( column [ ,...n ] )

指定要存储的列。非聚集列存储索引限制为 1,024 列。每个列都必须采用列存储索引支持的数据类型。有关受支持数据类型的列表，请参阅限制和局限。

ON [ database_name. [ schema_name ] . | schema_name . ] table_name

指定包含该索引的由一部分、两部分或三部分名称组成的表。

WITH 选项

DROP_EXISTING = [OFF] | ON

DROP_EXISTING = ON：删除并重新生成现有索引。指定的索引名称必须与当前的现有索引相同；但可以修改索引定义。例如，可以指定不同的列或索引选项。

DROP_EXISTING = OFF
如果指定的索引名称已存在，则会显示错误。使用 DROP_EXISTING 不能更改索引类型。在向后兼容的语法中，WITH DROP_EXISTING 等效于 WITH DROP_EXISTING = ON。

MAXDOP = max_degree_of_parallelism

在索引操作期间替代“配置最大并行度”（服务器配置选项）配置选项。使用 MAXDOP 可以限制在执行并行计划的过程中使用的处理器数量。最大数量为 64 个处理器。

max_degree_of_parallelism 可为以下值：

1，这表示取消生成并行计划。
>1，这表示基于当前系统工作负载，将并行索引操作中使用的最大处理器数限制为指定数量或更少。例如，当 MAXDOP = 4 时，使用的处理器数为 4 或更少。
0（默认值），这表示根据当前系统工作负载使用实际的处理器数量或更少数量的处理器。

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

注意

并非每个版本的 Microsoft SQL Server 都提供并行索引操作。有关 SQL Server 各个版次支持的功能列表，请参阅 SQL Server 2022 的各个版次及其支持的功能。

ONLINE = [ON | OFF]

ON 指定列存储索引在生成新副本时保持联机并可用。
OFF 指定索引在生成新副本时不可用。在非聚集索引中，基表仍然可用。只不过非聚集列存储索引在新索引完成前不能用于满足查询。

CREATE COLUMNSTORE INDEX ncci ON Sales.OrderLines (StockItemID, Quantity, UnitPrice, TaxRate)
WITH ( ONLINE = ON );

COMPRESSION_DELAY = 0 | delay [ MINUTES ]

指定某一行在适合迁移到压缩行组之前，应在增量行组中保留的时间下限。例如，可以说，如果某一行在 120 分钟内保持不变，则该行可以压缩为列存储格式。

对于基于磁盘的表中的列存储索引，不跟踪行的插入或更新时间，而是使用增量行组关闭时间作为行代理。默认持续时间为 0 分钟。行在增量行组中累积 100 万行后迁移到列存储，并标记为已关闭。

DATA_COMPRESSION

为指定的表、分区号或分区范围指定数据压缩选项。仅适用于列存储索引，包括非聚集列存储索引和聚集列存储索引。选项如下：

COLUMNSTORE 是默认值，它指定使用性能最高的列存储压缩进行压缩。此操作是典型选择。
COLUMNSTORE_ARCHIVE 将表或分区进一步压缩为更小的大小。此选项可用于存档，或者用于要求更小存储大小并且可以付出更多时间来进行存储和检索的其他情形。

有关压缩的详细信息，请参阅数据压缩。

WHERE <filter_expression> [ AND <filter_expression> ]

此选项称为筛选器谓词，指定哪些行包含在索引中。 SQL Server 对筛选索引中的数据行创建筛选统计信息。

该筛选器谓词使用简单的比较逻辑。不允许使用 NULL 文本的比较运算符。而改用 IS NULL 和 IS NOT NULL 运算符。

下面是一些 Production.BillOfMaterials 表筛选谓词示例：

WHERE StartDate > '20000101' AND EndDate <= '20000630'
WHERE ComponentID IN (533, 324, 753)
WHERE StartDate IN ('20000404', '20000905') AND EndDate IS NOT NULL

有关筛选索引的指南，请参阅创建筛选索引。

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)。

权限

需要对表的 ALTER 权限。

注解

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

筛选索引

筛选索引是一种经过优化的非聚集索引，适用于从表中选择少数行的查询。筛选索引使用筛选谓词对表中的部分数据进行索引。设计良好的筛选索引可以提高查询性能，降低存储成本和维护成本。

筛选索引所需的 SET 选项

如果下列任何条件成立，则需要“必需的值”列中的 SET 选项：

创建筛选索引。
INSERT、UPDATE、DELETE 或 MERGE 操作修改筛选索引中的数据。
查询优化器使用该筛选索引生成查询计划。

SET 选项	所需的值	默认服务器值	默认 OLE DB 和 ODBC 值	默认 DB-Library 值
ANSI_NULLS	ON	ON	ON	OFF
ANSI_PADDING	ON	ON	ON	OFF
ANSI_WARNINGS ¹	ON	ON	ON	OFF
ARITHABORT	ON	ON	OFF	OFF
CONCAT_NULL_YIELDS_NULL	ON	ON	ON	OFF
NUMERIC_ROUNDABORT	OFF	OFF	OFF	OFF
QUOTED_IDENTIFIER	ON	ON	ON	OFF

¹ 当数据库兼容性级别设置为 90 或更高时，如果将 ANSI_WARNINGS 设置为 ON，则将使 ARITHABORT 隐式设置为 ON。如果数据库兼容性级别设置为 80 或更低，则必须将 ARITHABORT 选项显式设置为 ON。

如果 SET 选项不正确，则可能会出现以下情况：

不会创建筛选索引。
数据库引擎生成错误并回滚对索引中的数据进行更改的 INSERT、UPDATE、DELETE 或 MERGE 语句。
查询优化器不考虑任何 Transact-SQL 语句的执行计划中的索引。

有关筛选索引的详细信息，请参阅创建筛选索引。

限制和局限

列存储索引中的每一列都必须是以下常见业务数据类型之一：

datetimeoffset [ ( n ) ]
datetime2 [ ( n ) ]
datetime
smalldatetime
date
time [ ( n ) ]
float [ ( n ) ]
real [ ( n ) ]
decimal [ ( precision [ , scale ] ) ]
numeric [ ( precision [ , scale ] ) ]
money
smallmoney
bigint
int
smallint
tinyint
bit
nvarchar [ ( n ) ]
nvarchar(max)¹
nchar [ ( n ) ]
varchar [ ( n ) ]
varchar(max)¹
char [ ( n ) ]
varbinary [ ( n ) ]
varbinary(max)¹
binary [ ( n ) ]
uniqueidentifier²

¹ 适用于 SQL Server 2017 (14.x) 以及高级层、标准层（S3 及更高）和所有 vCore 产品/服务层的 Azure SQL 数据库，仅限聚集列存储索引。

² 适用于 SQL Server 2014 (12.x) 及更高版本。

如果基础表中包含的某一列的数据类型不受列存储索引支持，则必须从非聚集列存储索引中省略该列。

大于 8 KB 的大型对象 (LOB) 数据存储在行外 LOB 存储中（只使用指向列段中存储的物理位置的指针）。存储的数据的大小不会在 sys.column_store_segments、sys.column_store_dictionaries 或 sys.dm_db_column_store_row_group_physical_stats 中进行报告。

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

ntext、text 和 image
nvarchar(max)、varchar(max) 和 varbinary(max)¹
rowversion（和 timestamp）
sql_variant
CLR 类型（hierarchyid 和空间类型）
xml
uniqueidentifier²

¹ 适用于 SQL Server 2016 (13.x) 和早期版本，以及非聚集列存储索引。

² 适用于 SQL Server 2012 (11.x)。

非聚集列存储索引：

不能超过 1,024 列。
无法创建为基于约束的索引。对于具有列存储索引的表，可以具有唯一约束、主键约束和外键约束。总是通过行存储索引强制执行约束。无法使用列存储（群集或非群集）索引强制执行约束。
不能包含稀疏列。
不能使用 ALTER INDEX 语句进行更改。若要更改非聚集索引，必须先删除该列存储索引，然后重新创建它。可以使用 ALTER INDEX 禁用并重新生成列存储索引。
不能使用 INCLUDE 关键字创建。
不能包括用来对索引排序的 ASC 或 DESC 关键字。根据压缩算法对列存储索引排序。排序将抵销许多性能优势。在 Azure Synapse Analytics 中，从 SQL Server 2022 (16.x) 开始，可以为列存储索引中的列指定顺序。有关详细信息，请参阅使用有序聚集列存储索引优化性能。
不能在非聚集列存储索引中包含 nvarchar（max）、varchar（max）和 varbinary（max）类型的 LOB 列。仅聚集列存储索引支持 LOB 类型，从 SQL Server 2017 （14.x）版本开始，Azure SQL 数据库（在高级版层、标准层（S3 及更高版本）和所有 vCore 产品/服务层级进行配置）。以前的版本不支持聚集列存储索引和非聚集列存储索引中的 LOB 类型。
从 SQL Server 2016 (13.x) 开始，可以在索引视图上创建非聚集列存储索引。

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

计算列。从 SQL Server 2017 （14.x）开始，聚集列存储索引可以包含非持久化计算列。但是，在 SQL Server 2017（14.x）中，聚集列存储索引不能包含持久化计算列，并且无法在计算列上创建非聚集索引。
页面和行压缩以及 vardecimal 存储格式。（列存储索引已采用不同格式压缩。）
复制。
文件流。

不能在具有聚集列存储索引的表中使用游标或触发器。此限制不适用于非聚集列存储索引。可以在具有非聚集列存储索引的表中使用游标和触发器。

SQL Server 2014 (12.x) 特定限制：

以下限制仅适用于 SQL Server 2014 (12.x)。在此版本中，你可使用可更新的聚集列存储索引。非聚集列存储索引仍为只读。

更改跟踪。不能将更改跟踪与列存储索引配合使用。
变更数据捕获。不能对具有聚集列存储索引的表启用此功能。从 SQL Server 2016 （13.x）开始，可以在具有非聚集列存储索引的表上启用更改数据捕获。
可读辅助副本。不能通过 Always On 可读可用性组的可读辅助副本访问聚集列存储索引 (CCI)。可以通过可读辅助副本访问非聚集列存储索引 (NCCI)。
多重活动结果集 (MARS)。 SQL Server 2014 (12.x) 使用此功能对包含列存储索引的表执行只读连接。不过，SQL Server 2014 (12.x) 不支持使用此功能对包含列存储索引的表执行并发数据操作语言 (DML) 操作。如果尝试使用该功能来实现此目的，SQL Server 将终止连接并取消事务。
无法在视图或索引视图上创建非聚集列存储索引。

有关列存储索引的性能优势和限制的信息，请参阅列存储索引：概述。

元数据

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

示例；将表从行存储转换为列存储

A. 将堆转换为聚集列存储索引

此示例将一个表作为堆创建，然后将其转换为名为 cci_Simple 的聚集列存储索引。创建聚集列存储索引会将整个表的存储从行存储更改为列存储。

CREATE TABLE dbo.SimpleTable(
    ProductKey [INT] NOT NULL,
    OrderDateKey [INT] NOT NULL,
    DueDateKey [INT] NOT NULL,
    ShipDateKey [INT] NOT NULL);
GO
CREATE CLUSTERED COLUMNSTORE INDEX cci_Simple ON dbo.SimpleTable;
GO

B. 将聚集索引转换为具有相同名称的聚集列存储索引

此示例创建一个具有聚集索引的表，然后演示将该聚集索引转换为聚集列存储索引的语法。创建聚集列存储索引会将整个表的存储从行存储更改为列存储。

CREATE TABLE dbo.SimpleTable2 (
    ProductKey [INT] NOT NULL,
    OrderDateKey [INT] NOT NULL,
    DueDateKey [INT] NOT NULL,
    ShipDateKey [INT] NOT NULL);
GO
CREATE CLUSTERED INDEX cl_simple ON dbo.SimpleTable2 (ProductKey);
GO
CREATE CLUSTERED COLUMNSTORE INDEX cl_simple ON dbo.SimpleTable2
WITH (DROP_EXISTING = ON);
GO

C. 将行存储表转换为列存储索引时处理非聚集索引

此示例展示如何在将行存储表转换为列存储索引时处理非聚集索引。从 SQL Server 2016 (13.x) 开始，无需执行特别的操作。 SQL Server 会在新的聚集列存储索引上自动定义并重新生成非聚集索引。

如果要删除非聚集索引，请先使用 DROP INDEX 语句，然后再创建列存储索引。 DROP EXISTING 选项仅删除正在转换的聚集索引。它不会删除非聚集索引。

在 SQL Server 2012 (11.x) 和 SQL Server 2014 (12.x) 中，无法在列存储索引上创建非聚集索引。

--Create the table for use with this example.
CREATE TABLE dbo.SimpleTable (
    ProductKey [INT] NOT NULL,
    OrderDateKey [INT] NOT NULL,
    DueDateKey [INT] NOT NULL,
    ShipDateKey [INT] NOT NULL);
GO
  
--Create two nonclustered indexes for use with this example
CREATE INDEX nc1_simple ON dbo.SimpleTable (OrderDateKey);
CREATE INDEX nc2_simple ON dbo.SimpleTable (DueDateKey);
GO

仅对于 SQL Server 2012 (11.x) 和 SQL Server 2014 (12.x)，才必须删除非聚集索引，以便创建列存储索引。

DROP INDEX dbo.SimpleTable.nc1_simple;
DROP INDEX dbo.SimpleTable.nc2_simple;
  
--Convert the rowstore table to a columnstore index.
CREATE CLUSTERED COLUMNSTORE INDEX cci_simple ON dbo.SimpleTable;
GO

D. 将大型事实表从行存储转换为列存储方式

此示例说明如何将大型事实表从行存储表转换为列存储表。

创建要在此示例中使用的一个较小的表。

--Create a rowstore table with a clustered index and a nonclustered index.
CREATE TABLE dbo.MyFactTable (
    ProductKey [INT] NOT NULL,
    OrderDateKey [INT] NOT NULL,
    DueDateKey [INT] NOT NULL,
    ShipDateKey [INT] NOT NULL
INDEX IDX_CL_MyFactTable CLUSTERED  ( ProductKey )
);

--Add a nonclustered index.
CREATE INDEX my_index ON dbo.MyFactTable ( ProductKey, OrderDateKey );

从行存储表中删除所有非聚集索引。可能需要编写索引脚本，以便以后重新创建它们。
```
--Drop all nonclustered indexes
DROP INDEX my_index ON dbo.MyFactTable;
```

将行存储表转换为具有聚集列存储索引的列存储表。

首先，查找现有聚集行存储索引的名称。在步骤 1 中，我们将索引的名称设置为 IDX_CL_MyFactTable。如果未指定索引名称，则会为其提供自动生成的唯一索引名称。可以使用以下示例查询检索自动生成的名称：

SELECT i.object_id, i.name, t.object_id, t.name
FROM sys.indexes i
INNER JOIN sys.tables t ON i.object_id = t.object_id
WHERE i.type_desc = 'CLUSTERED'
AND t.name = 'MyFactTable';

选项 1：删除现有聚集索引 IDX_CL_MyFactTable 并将 MyFactTable 转换为列存储。更改新聚集列存储索引的名称。

--Drop the clustered rowstore index.
DROP INDEX [IDX_CL_MyFactTable]
ON dbo.MyFactTable;
GO
--Create a new clustered columnstore index with the name MyCCI.
CREATE CLUSTERED COLUMNSTORE
INDEX IDX_CCL_MyFactTable ON dbo.MyFactTable;
GO

选项 2：转换为列存储，并重复使用现有行存储聚集索引名称。

--Create the clustered columnstore index,
--replacing the existing rowstore clustered index of the same name
CREATE CLUSTERED COLUMNSTORE
INDEX [IDX_CL_MyFactTable]
ON dbo.MyFactTable
WITH (DROP_EXISTING = ON);

E. 将列存储表转换为具有聚集索引的行存储表

要将列存储表转换为具有聚集索引的行存储表，请使用带 DROP_EXISTING 选项的 CREATE INDEX 语句。

CREATE CLUSTERED INDEX [IDX_CL_MyFactTable]
ON dbo.[MyFactTable] ( ProductKey )
WITH ( DROP_EXISTING = ON );

F. 将列存储表转换为行存储堆

要将列存储表转换为行存储堆，请删除聚集列存储索引。通常不建议这样做，但有些可能用途有限。有关堆的详细信息，请参阅堆（没有聚集索引的表）。

DROP INDEX [IDX_CL_MyFactTable]
ON dbo.[MyFactTable];

G. 通过重新组织列存储索引进行碎片整理

有两种方法可维护聚集列存储索引。从 SQL Server 2016 (13.x) 开始，使用 ALTER INDEX...REORGANIZE 而不是 REBUILD。有关详细信息，请参阅列存储索引行组。在以前的 SQL Server 版本中，可以使用 CREATE CLUSTERED COLUMNSTORE INDEX (DROP_EXISTING=ON)，或是 ALTER INDEX (Transact-SQL) 和 REBUILD 选项。这两种方法可以得到相同的结果。

首先在 MyFactTable 中确定聚集列存储索引名称。

SELECT i.object_id, i.name, t.object_id, t.name
FROM sys.indexes i
INNER JOIN sys.tables t on i.object_id = t.object_id
WHERE i.type_desc = 'CLUSTERED COLUMNSTORE'
AND t.name = 'MyFactTable';

通过对列存储索引执行 REORGANIZE 来移除碎片。

--Rebuild the entire index by using ALTER INDEX and the REBUILD option.
ALTER INDEX IDX_CL_MyFactTable
ON dbo.[MyFactTable]
REORGANIZE;

非聚集列存储索引示例

A. 对行存储表创建列存储索引作为辅助索引

此示例会对行存储表创建非聚集列存储索引。在这种情况下只能创建一个列存储索引。列存储索引需要额外的存储空间，因为它包含行存储表中数据的副本。此示例会创建一个简单表和行存储聚集索引，然后演示创建非聚集列存储索引的语法。

CREATE TABLE dbo.SimpleTable (
    ProductKey [INT] NOT NULL,
    OrderDateKey [INT] NOT NULL,
    DueDateKey [INT] NOT NULL,
    ShipDateKey [INT] NOT NULL);
GO

CREATE CLUSTERED INDEX cl_simple ON dbo.SimpleTable (ProductKey);
GO

CREATE NONCLUSTERED COLUMNSTORE INDEX csindx_simple
ON dbo.SimpleTable (OrderDateKey, DueDateKey, ShipDateKey);
GO

B. 使用所有选项创建基本的非聚集列存储索引

以下示例演示了对 DEFAULT 文件组创建非聚集列存储索引的语法（将最大并行度 (MAXDOP) 指定为 2）。

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

C. 使用筛选谓词创建非聚集列存储索引

以下示例对 AdventureWorks2022 示例数据库中的 Production.BillOfMaterials 表创建已筛选的非聚集列存储索引。筛选谓词可包含那些不是筛选索引中的键列的列。本示例中的谓词将仅选择其中的 EndDate 为非 NULL 的行。

IF EXISTS (SELECT name FROM sys.indexes
    WHERE name = N'FIBillOfMaterialsWithEndDate'
    AND object_id = OBJECT_ID(N'Production.BillOfMaterials'))
DROP INDEX FIBillOfMaterialsWithEndDate
    ON Production.BillOfMaterials;
GO
CREATE NONCLUSTERED COLUMNSTORE INDEX "FIBillOfMaterialsWithEndDate"
    ON Production.BillOfMaterials (ComponentID, StartDate)
    WHERE EndDate IS NOT NULL;

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

适用范围：SQL Server 2012 (11.x) 到 SQL Server 2014 (12.x)。

在 SQL Server 2014（12.x）和早期版本中，在表上创建非聚集列存储索引后，不能直接修改该表中的数据。具有 INSERT、UPDATE、DELETE 或 MERGE 的查询会失败并且返回错误消息。下面是可用于在表中添加或修改数据的选项：

禁用或删除列存储索引。然后可以更新表中的数据。如果禁用列存储索引，则可以在完成数据更新后重新生成列存储索引。例如：
```
ALTER INDEX mycolumnstoreindex ON dbo.mytable DISABLE;
-- update the data in mytable as necessary
ALTER INDEX mycolumnstoreindex on dbo.mytable REBUILD;
```
将数据加载到不含列存储索引的临时表中。在临时表上生成列存储索引。将临时表切换到主表的一个空分区中。
将分区从具有列存储索引的表切换到一个空的临时表中。如果在临时表上有某个列存储索引，则禁用该列存储索引。执行任何更新。生成（或重新生成）列存储索引。将临时表切换回主表的（现在为空的）分区中。

示例：Azure Synapse Analytics、Analytics Platform System (PDW)

A. 将聚集索引更改为聚集列存储索引

通过使用 DROP_EXISTING = ON 的 CREATE CLUSTERED COLUMNSTORE INDEX 语句，可以：

将聚集索引更改为聚集列存储索引。
重新生成聚集列存储索引。

此示例将 xDimProduct 表创建为具有聚集索引的行存储表。然后该示例使用 CREATE CLUSTERED COLUMNSTORE INDEX 将表从行存储表更改为列存储表。

-- Uses AdventureWorks
  
IF EXISTS (SELECT name FROM sys.tables
    WHERE name = N'xDimProduct'
    AND object_id = OBJECT_ID (N'xDimProduct'))
DROP TABLE xDimProduct;
  
--Create a distributed table with a clustered index.
CREATE TABLE xDimProduct (ProductKey, ProductAlternateKey, ProductSubcategoryKey)
WITH ( DISTRIBUTION = HASH(ProductKey),
    CLUSTERED INDEX (ProductKey) )
AS SELECT ProductKey, ProductAlternateKey, ProductSubcategoryKey FROM DimProduct;

使用 sys.indexes，在系统元数据中查找为新表自动创建的聚集索引的名称。例如：

SELECT i.object_id, i.name, t.object_id, t.name, i.type_desc
FROM sys.indexes i
INNER JOIN sys.tables t ON i.object_id = t.object_id
WHERE i.type_desc = 'CLUSTERED'
AND t.name = 'xdimProduct';

现在，可以选择：

删除具有自动创建名称的现有聚集列存储索引，然后创建具有用户定义名称的新聚集列存储索引。
删除现有索引并将其替换为聚集列存储索引，保持系统生成的名称相同，例如 ClusteredIndex_1bd8af8797f7453182903cc68df48541。

例如：

--1. DROP the existing clustered columnstore index with an automatically-created name, for example:
DROP INDEX ClusteredIndex_1bd8af8797f7453182903cc68df48541 on xdimProduct;
GO
CREATE CLUSTERED COLUMNSTORE INDEX [<new_index_name>]
ON xdimProduct;
GO

--Or,
--2. Change the existing clustered index to a clustered columnstore index with the same name.
CREATE CLUSTERED COLUMNSTORE INDEX [ClusteredIndex_1bd8af8797f7453182903cc68df48541]
ON xdimProduct
WITH ( DROP_EXISTING = ON );
GO

B. 重新生成聚集列存储索引

基于上一示例，此示例使用 CREATE CLUSTERED COLUMNSTORE INDEX 重新生成名为 cci_xDimProduct 的现有聚集列存储索引。

--Rebuild the existing clustered columnstore index.
CREATE CLUSTERED COLUMNSTORE INDEX cci_xDimProduct
ON xdimProduct
WITH ( DROP_EXISTING = ON );

C. 更改聚集列存储索引的名称

要更改聚集列存储索引的名称，请删除现有的聚集列存储索引，然后使用新名称重新创建索引。

建议将此操作限制在小型表或空表中。删除大型聚集列存储索引并使用其他名称重新生成需要很长时间。

此示例引用上一示例中的 cci_xDimProduct 聚集列存储索引。此示例将删除 cci_xDimProduct 聚集列存储索引，然后使用名称 mycci_xDimProduct 重新创建聚集列存储索引。

--For illustration purposes, drop the clustered columnstore index.
--The table continues to be distributed, but changes to a heap.
DROP INDEX cci_xdimProduct ON xDimProduct;
  
--Create a clustered index with a new name, mycci_xDimProduct.
CREATE CLUSTERED COLUMNSTORE INDEX mycci_xDimProduct
ON xdimProduct
WITH ( DROP_EXISTING = OFF );

D. 将列存储表转换为具有聚集索引的行存储表

可能会出现想删除聚集列存储索引并创建聚集索引的情况。删除聚集列存储索引时，表会更改为行存储格式。此示例会将列存储表转换为具有同名聚集索引的行存储表。不会丢失任何数据。所有数据都转到行存储表，列出的列将成为聚集索引中的键列。

--Drop the clustered columnstore index and create a clustered rowstore index.
--All of the columns are stored in the rowstore clustered index.
--The columns listed are the included columns in the index.
CREATE CLUSTERED INDEX cci_xDimProduct
ON xdimProduct (ProductKey, ProductAlternateKey, ProductSubcategoryKey, WeightUnitMeasureCode)
WITH ( DROP_EXISTING = ON);

E. 将列存储表转换回行存储堆

使用 DROP INDEX (SQL Server PDW) 删除聚集列存储索引并将表转换为行存储堆。此示例会将 cci_xDimProduct 表转换为行存储堆。可继续分配该表，但将其存储为堆。

--Drop the clustered columnstore index. The table continues to be distributed, but changes to a heap.
DROP INDEX cci_xdimProduct ON xdimProduct;

F. 在不具有索引的表上创建有序聚集列存储索引

默认情况下，无序列存储索引会涵盖所有列，而无需指定列的列表。有序列存储索引允许指定列的顺序。该列表不需要包括所有列。

有序列存储索引在 Azure Synapse Analytics、Analytics Platform System (PDW) 和 SQL Server 2022 (16.x) 中可用。有关详细信息，请参阅使用有序聚集列存储索引优化性能。

CREATE CLUSTERED COLUMNSTORE INDEX cci ON Sales.OrderLines
ORDER (SHIPDATE);

G. 将聚集列存储索引转换为有序聚集列存储索引

CREATE CLUSTERED COLUMNSTORE INDEX cci ON Sales.OrderLines
ORDER (SHIPDATE)
WITH (DROP_EXISTING = ON);

H. 向有序聚集列存储索引的排序添加列

在 Azure Synapse Analytics、Analytics Platform System (PDW) 中，从 SQL Server 2022 (16.x) 开始，可以为列存储索引中的列指定顺序。原始有序聚集列存储索引仅按 SHIPDATE 列排序。以下示例将 PRODUCTKEY 列添加到排序中。

CREATE CLUSTERED COLUMNSTORE INDEX cci ON Sales.OrderLines
ORDER (SHIPDATE, PRODUCTKEY)
WITH (DROP_EXISTING = ON);

I. 更改有序列的序号

原始有序聚集列存储索引按 SHIPDATE、PRODUCTKEY 排序。下面的示例将排序更改为 PRODUCTKEY、SHIPDATE。

CREATE CLUSTERED COLUMNSTORE INDEX cci ON Sales.OrderLines
ORDER (PRODUCTKEY,SHIPDATE)
WITH (DROP_EXISTING = ON);

J. 创建有序聚集列存储索引

适用于：Azure Synapse Analytics 和 SQL Server 2022 (16.x)

可以使用排序键创建聚集列存储索引。创建有序聚集列存储索引时，应应用查询提示 MAXDOP = 1 ，以获得最大排序质量和最短持续时间。

CREATE CLUSTERED COLUMNSTORE INDEX [OrderedCCI] ON dbo.FactResellerSalesPartCategoryFull
ORDER (EnglishProductSubcategoryName, EnglishProductName)
WITH (MAXDOP = 1, DROP_EXISTING = ON);

CREATE COLUMNSTORE INDEX (Transact-SQL)

语法

参数

CREATE CLUSTERED COLUMNSTORE INDEX

index_name

ON [ database_name. [ schema_name ] . | schema_name . ] table_name

ORDER

WITH 选项

DROP_EXISTING = [OFF] | ON

MAXDOP = max_degree_of_parallelism

COMPRESSION_DELAY = 0 | delay [ MINUTES ]

DATA_COMPRESSION = COLUMNSTORE | COLUMNSTORE_ARCHIVE

ONLINE = [ON | OFF]

ON 选项

CREATE [NONCLUSTERED] COLUMNSTORE INDEX

index_name

( column [ ,...n ] )

ON [ database_name. [ schema_name ] . | schema_name . ] table_name

WITH 选项

DROP_EXISTING = [OFF] | ON

MAXDOP = max_degree_of_parallelism

ONLINE = [ON | OFF]

COMPRESSION_DELAY = 0 | delay [ MINUTES ]

DATA_COMPRESSION

WHERE <filter_expression> [ AND <filter_expression> ]

ON 选项

partition_scheme_name ( column_name )

filegroup_name

"default"

权限

注解

筛选索引

筛选索引所需的 SET 选项

限制和局限

元数据

示例；将表从行存储转换为列存储

A. 将堆转换为聚集列存储索引

B. 将聚集索引转换为具有相同名称的聚集列存储索引

C. 将行存储表转换为列存储索引时处理非聚集索引

D. 将大型事实表从行存储转换为列存储方式

E. 将列存储表转换为具有聚集索引的行存储表

F. 将列存储表转换为行存储堆

G. 通过重新组织列存储索引进行碎片整理

非聚集列存储索引示例

A. 对行存储表创建列存储索引作为辅助索引

B. 使用所有选项创建基本的非聚集列存储索引

C. 使用筛选谓词创建非聚集列存储索引

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

示例：Azure Synapse Analytics、Analytics Platform System (PDW)

A. 将聚集索引更改为聚集列存储索引

B. 重新生成聚集列存储索引

C. 更改聚集列存储索引的名称

D. 将列存储表转换为具有聚集索引的行存储表

E. 将列存储表转换回行存储堆

F. 在不具有索引的表上创建有序聚集列存储索引

G. 将聚集列存储索引转换为有序聚集列存储索引

H. 向有序聚集列存储索引的排序添加列

I. 更改有序列的序号

J. 创建有序聚集列存储索引

相关内容

反馈

反馈

其他资源