CREATE COLUMNSTORE INDEX (Transact-SQL)CREATE COLUMNSTORE INDEX (Transact-SQL)

适用对象: yesSQL ServeryesAzure SQL 数据库yesAzure SQL 数据仓库yes并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

将行存储表转换为聚集列存储索引,或创建非聚集列存储索引。Convert a rowstore table to a clustered columnstore index or create a nonclustered columnstore index. 使用列存储索引可对 OLTP 工作负载有效地运行实时运营分析,或提高数据仓库工作负载的数据压缩和查询性能。Use a columnstore index to efficiently run real-time operational analytics on an OLTP workload or to improve data compression and query performance for data warehousing workloads.

备注

SQL Server 2016 (13.x)SQL Server 2016 (13.x) 开始,可以将表创建为聚集列存储索引。Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x), you can create the table as a clustered columnstore index. 再也不需要先创建行存储表,然后将其转换为聚集列存储索引。It is no longer necessary to first create a rowstore table and then convert it to a clustered columnstore index.

提示

有关索引设计指南的信息,请参阅 SQL Server 索引设计指南For information on index design guidelines, refer to the SQL Server Index Design Guide.

跳转到示例:Skip to examples:

跳转到方案:Go to scenarios:

了解详细信息:Learn more:

主题链接图标 TRANSACT-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax

-- Syntax for SQL Server and Azure SQL Database  
  
-- 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 }  
    [ 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  
  
-- Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse  
  
CREATE CLUSTERED COLUMNSTORE INDEX index_name
    ON { database_name.schema_name.table_name | schema_name.table_name | table_name } 
    [ORDER (column [,...n] ) ] -- in preview
    [ WITH ( DROP_EXISTING = { ON | OFF } ) ] --default is OFF  
[;]  

参数Arguments

某些选项并非在所有数据库引擎版本中均可用。Some of the options are not available in all database engine versions. 下表显示了聚集列存储索引和非聚集列存储索引中引入选项时的版本:The following table shows the versions when the options are introduced in CLUSTERED COLUMNSTORE and NONCLUSTERED COLUMNSTORE indexes:

选项Option CLUSTEREDCLUSTERED NONCLUSTEREDNONCLUSTERED
COMPRESSION_DELAYCOMPRESSION_DELAY SQL Server 2016 (13.x)SQL Server 2016 (13.x) SQL Server 2016 (13.x)SQL Server 2016 (13.x)
DATA_COMPRESSIONDATA_COMPRESSION SQL Server 2016 (13.x)SQL Server 2016 (13.x) SQL Server 2016 (13.x)SQL Server 2016 (13.x)
ONLINEONLINE SQL Server 2019 (15.x)SQL Server 2019 (15.x) SQL Server 2017 (14.x)SQL Server 2017 (14.x)
WHERE 子句WHERE clause 空值N/A SQL Server 2016 (13.x)SQL Server 2016 (13.x)

所有选项在 Azure SQL 数据库中均可用。All options are available in Azure SQL Database.

CREATE CLUSTERED COLUMNSTORE INDEXCREATE CLUSTERED COLUMNSTORE INDEX

创建一个聚集列存储索引,并按列压缩和存储其中的所有数据。Create a clustered columnstore index in which all of the data is compressed and stored by column. 该索引包含表中的所有列,并且存储整个表。The index includes all of the columns in the table, and stores the entire table. 如果现有表是堆或聚集索引,则该表会转换为聚集列存储索引。If the existing table is a heap or clustered index, the table is converted to a clustered columnstore index. 如果该表已作为聚集列存储索引存储,则会删除并重新生成现有索引。If the table is already stored as a clustered columnstore index, the existing index is dropped and rebuilt.

index_nameindex_name
指定新索引的名称。Specifies the name for the new index.

如果该表已具有聚集列存储索引,则可以指定与现有索引相同的名称,也可以使用 DROP EXISTING 选项指定新名称。If the table already has a clustered columnstore index, you can specify the same name as the existing index, or you can use the DROP EXISTING option to specify a new name.

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

指定要作为聚集列存储索引存储的由一部分、两部分或三部分构成的名称。Specifies the one-, two-, or three-part name of the table to be stored as a clustered columnstore index. 如果该表是堆或聚集索引,则会将其从行存储转换为列存储。If the table is a heap or clustered index the table is converted from rowstore to a columnstore. 如果该表已经是列存储,则此语句会重新生成聚集列存储索引。If the table is already a columnstore, this statement rebuilds the clustered columnstore index. 若要转换为有序聚集列存储索引,现有索引必须是聚集列存储索引。To convert to an ordered clustered column store index the existing index must be a clustered columnstore index.

WITH 选项WITH options

DROP_EXISTING = [OFF] | ONDROP_EXISTING = [OFF] | ON

DROP_EXISTING = ON 指定删除现有的索引,并创建一个新的列存储索引。DROP_EXISTING = ON specifies to drop the existing index, and create a new columnstore index.

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

DROP_EXISTING = OFF(默认值)要求索引名称与现有名称相同。The default, DROP_EXISTING = OFF expects the index name is the same as the existing name. 如果指定的索引名称已存在,则会出错。An error occurs is the specified index name already exists.

MAXDOP = max_degree_of_parallelism MAXDOP = max_degree_of_parallelism

在索引操作期间覆盖现有的最大并行度服务器配置。Overrides the existing maximum degree of parallelism server configuration for the duration of the index operation. 使用 MAXDOP 可以限制在执行并行计划的过程中使用的处理器数量。Use MAXDOP to limit the number of processors used in a parallel plan execution. 最大数量为 64 个处理器。The maximum is 64 processors.

max_degree_of_parallelism 可为以下值 :max_degree_of_parallelism values can be:

  • 1 - 取消生成并行计划。1 - Suppress parallel plan generation.
  • >1 - 基于当前系统工作负载,将并行索引操作中使用的最大处理器数限制为指定数量或更少。>1 - Restrict the maximum number of processors used in a parallel index operation to the specified number or fewer based on the current system workload. 例如,当 MAXDOP = 4 时,使用的处理器数为 4 或更少。For example, when MAXDOP = 4, the number of processors used is 4 or less.
  • 0(默认值) - 根据当前系统工作负荷使用实际的处理器数量或更少数量的处理器。0 (default) - Use the actual number of processors or fewer based on the current system workload.
CREATE CLUSTERED COLUMNSTORE INDEX cci ON Sales.OrderLines
       WITH (MAXDOP = 2);

有关详细信息,请参阅配置 max degree of parallelism 服务器配置选项配置并行索引操作For more information, see Configure the max degree of parallelism Server Configuration Option, and Configure Parallel Index Operations.

COMPRESSION_DELAY = 0 | delay [ Minutes ]COMPRESSION_DELAY = 0 | delay [ Minutes ]

对于基于磁盘的表,delay 指定处于关闭状态的增量行组在 SQL Server 可以将它压缩为压缩行组之前,必须保持为增量行组的最小分钟数 。For a disk-based table, delay specifies the minimum number of minutes a delta rowgroup in the CLOSED state must remain in the delta rowgroup before SQL Server can compress it into the compressed rowgroup. 由于基于磁盘的表不对单个行跟踪插入和更新时间,因此 SQL Server 会将该延迟应用于处于关闭状态的增量行组。Since disk-based tables don't track insert and update times on individual rows, SQL Server applies the delay to delta rowgroups in the CLOSED state.
默认为 0 分钟。The default is 0 minutes.

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

有关何时使用 COMPRESSION_DELAY 的建议,请参阅开始使用列存储进行实时运行分析For recommendations on when to use COMPRESSION_DELAY, see Get started with Columnstore for real time operational analytics.

DATA_COMPRESSION = COLUMNSTORE | COLUMNSTORE_ARCHIVEDATA_COMPRESSION = COLUMNSTORE | COLUMNSTORE_ARCHIVE

为指定的表、分区号或分区范围指定数据压缩选项。Specifies the data compression option for the specified table, partition number, or range of partitions. 选项如下所示:The options are as follows:

  • COLUMNSTORE 是默认值,它指定使用性能最高的列存储压缩进行压缩。COLUMNSTORE is the default and specifies to compress with the most performant columnstore compression. 这是典型选择。This is the typical choice.
  • COLUMNSTORE_ARCHIVE 将表或分区进一步压缩为更小的大小。COLUMNSTORE_ARCHIVE further compresses the table or partition to a smaller size. 可在许多情况下使用此选项,例如,用于要求存储更小并且可以付出更多时间来进行存储和检索的存档。Use this option for situations such as archival that require a smaller storage size and can afford more time for storage and retrieval.
CREATE CLUSTERED COLUMNSTORE INDEX cci ON Sales.OrderLines
       WITH ( DATA_COMPRESSION = COLUMNSTORE_ARCHIVE );

有关压缩的详细信息,请参阅数据压缩For more information about compression, see Data Compression.

ONLINE = [ON | OFF]ONLINE = [ON | OFF]
  • ON 指定列存储索引在生成新副本时保持联机并可用。ON specifies that the columnstore index remains online and available while the new copy of the index is being built.
  • OFF 指定索引在生成新副本时不可用。OFF specifies that the index is not available for use while the new copy is being built.
CREATE CLUSTERED COLUMNSTORE INDEX cci ON Sales.OrderLines
       WITH ( ONLINE = ON );

ON 选项ON options

使用 ON 选项,您可为数据存储指定选项,例如分区架构、特定的文件组或默认文件组。With the ON options you can specify options for data storage, such as a partition scheme, a specific filegroup, or the default filegroup. 如果未指定 ON 选项,索引会使用现有表的分区设置或文件组设置。If the ON option is not specified, the index uses the settings partition or filegroup settings of the existing table.

partition_scheme_name ( column_name ) partition_scheme_name ( column_name )
指定表的分区方案。Specifies the partition scheme for the table. 分区方案必须已在数据库中存在。The partition scheme must already exist in the database. 若要创建分区方案,请参阅 CREATE PARTITION SCHEMETo create the partition scheme, see CREATE PARTITION SCHEME.

column_name 指定对已分区索引进行分区所依据的列。column_name specifies the column against which a partitioned index is partitioned. 该列必须与 partition_scheme_name 使用的分区函数参数的数据类型、长度和精度相匹配 。This column must match the data type, length, and precision of the argument of the partition function that partition_scheme_name is using.

filegroup_name filegroup_name
指定用于存储聚集列存储索引的文件组。Specifies the filegroup for storing the clustered columnstore index. 如果未指定位置并且表未分区,则索引将与基础表或视图使用相同的文件组。If no location is specified and the table is not partitioned, the index uses the same filegroup as the underlying table or view. 该文件组必须已存在。The filegroup must already exist.

"default" " default "
若要对默认文件组创建索引,请使用 "default" 或 [ default ]。To create the index on the default filegroup, use "default" or [ default ].

如果指定了 "default",则当前会话的 QUOTED_IDENTIFIER 选项必须为 ON。If "default" is specified, the QUOTED_IDENTIFIER option must be ON for the current session. QUOTED_IDENTIFIER 默认为 ON。QUOTED_IDENTIFIER is ON by default. 有关详细信息,请参阅 SET QUOTED_IDENTIFIER (Transact-SQL)For more information, see SET QUOTED_IDENTIFIER (Transact-SQL).

CREATE [NONCLUSTERED] COLUMNSTORE INDEXCREATE [NONCLUSTERED] COLUMNSTORE INDEX

对存储为堆或聚集索引的行存储表创建内存中非聚集列存储索引。Create an in-memory nonclustered columnstore index on a rowstore table stored as a heap or clustered index. 该索引可以具有经过筛选的条件,并且不需要包含基础表的所有列。The index can have a filtered condition and does not need to include all of the columns of the underlying table. 列存储索引需要足够的空间来存储数据副本。The columnstore index requires enough space to store a copy of the data. 它是可更新的,在基础表发生更改时会进行更新。It is updateable and is updated as the underlying table is changed. 聚集索引上的非聚集列存储索引可启用实时分析。The nonclustered columnstore index on a clustered index enables real-time analytics.

index_name index_name
指定索引的名称。Specifies the name of the index. index_name 在表中必须唯一,但在数据库中不必唯一。index_name must be unique within the table, but does not have to be unique within the database. 索引名称必须符合标识符的规则。Index names must follow the rules of identifiers.

( column [ ,...n ] ) ( column [ ,...n ] )
指定要存储的列。Specifies the columns to store. 非聚集列存储索引限定为 1024 个列。A nonclustered columnstore index is limited to 1024 columns.
每个列都必须采用列存储索引支持的数据类型。Each column must be of a supported data type for columnstore indexes. 有关受支持数据类型的列表,请参阅限制和局限See Limitations and Restrictions for a list of the supported data types.

ON [database_name. ON [database_name. [schema_name ] . [schema_name ] . | schema_name . | schema_name . ] table_name] table_name
指定包含该索引的由一部分、两部分或三部分名称组成的表。Specifies the one-, two-, or three-part name of the table that contains the index.

WITH 选项WITH options

DROP_EXISTING = [OFF] | ONDROP_EXISTING = [OFF] | ON

DROP_EXISTING = ON:删除并重新生成现有索引。DROP_EXISTING = ON The existing index is dropped and rebuilt. 指定的索引名称必须与当前的现有索引相同;但可以修改索引定义。The index name specified must be the same as a currently existing index; however, the index definition can be modified. 例如,可以指定不同的列或索引选项。For example, you can specify different columns, or index options.

DROP_EXISTING = OFF:如果指定的索引名称已存在,则会显示一条错误。DROP_EXISTING = OFF An error is displayed if the specified index name already exists. 使用 DROP_EXISTING 不能更改索引类型。The index type cannot be changed by using DROP_EXISTING. 在向后兼容的语法中,WITH DROP_EXISTING 等效于 WITH DROP_EXISTING = ON。In backward compatible syntax, WITH DROP_EXISTING is equivalent to WITH DROP_EXISTING = ON.

MAXDOP = max_degree_of_parallelism MAXDOP = max_degree_of_parallelism

在索引操作期间覆盖配置 max degree of parallelism 服务器配置选项配置选项。Overrides the Configure the max degree of parallelism Server Configuration Option configuration option for the duration of the index operation. 使用 MAXDOP 可以限制在执行并行计划的过程中使用的处理器数量。Use MAXDOP to limit the number of processors used in a parallel plan execution. 最大数量为 64 个处理器。The maximum is 64 processors.

max_degree_of_parallelism 可为以下值 :max_degree_of_parallelism values can be:

  • 1 - 取消生成并行计划。1 - Suppress parallel plan generation.
  • >1 - 基于当前系统工作负载,将并行索引操作中使用的最大处理器数限制为指定数量或更少。>1 - Restrict the maximum number of processors used in a parallel index operation to the specified number or fewer based on the current system workload. 例如,当 MAXDOP = 4 时,使用的处理器数为 4 或更少。For example, when MAXDOP = 4, the number of processors used is 4 or less.
  • 0(默认值) - 根据当前系统工作负荷使用实际的处理器数量或更少数量的处理器。0 (default) - Use the actual number of processors or fewer based on the current system workload.

有关详细信息,请参阅 配置并行索引操作For more information, see Configure Parallel Index Operations.

备注

并非在 MicrosoftMicrosoftSQL ServerSQL Server 的每个版本中均支持并行索引操作。Parallel index operations are not available in every edition of MicrosoftMicrosoftSQL ServerSQL Server. 有关 SQL ServerSQL Server 各版本支持的功能列表,请参阅 SQL Server 2016 的版本和支持的功能For a list of features that are supported by the editions of SQL ServerSQL Server, see Editions and Supported Features for SQL Server 2016.

ONLINE = [ON | OFF]ONLINE = [ON | OFF]
  • ON 指定列存储索引在生成新副本时保持联机并可用。ON specifies that the columnstore index remains online and available while the new copy of the index is being built.
  • OFF 指定索引在生成新副本时不可用。OFF specifies that the index is not available for use while the new copy is being built. 在非聚集索引中,基表仍然可用,只不过非聚集列存储索引在新索引完成前不能用于满足查询。In nonclustered index, the base table remains available, only the nonclustered columnstore index is not used to satisfy queries until the new index is complete.
CREATE COLUMNSTORE INDEX ncci ON Sales.OrderLines (StockItemID, Quantity, UnitPrice, TaxRate) WITH ( ONLINE = ON );
COMPRESSION_DELAY = 0 | <delay>[Minutes]COMPRESSION_DELAY = 0 | <delay>[Minutes]

指定某一行在适合迁移到压缩行组之前,应在增量行组中保留的时间下限。Specifies a lower bound on how long a row should stay in delta rowgroup before it is eligible for migration to compressed rowgroup. 例如,客户可以说,如果某一行在 120 分钟内保持不变,则可以将其压缩为列存储格式。For example, a customer can say that if a row is unchanged for 120 minutes, make it eligible for compressing into columnar storage format. 对于基于磁盘的表中的列存储索引,我们不跟踪行的插入或更新时间,而是使用增量行组关闭时间作为行代理。For columnstore index on disk-based tables, we don't track the time when a row was inserted or updated, we use the delta rowgroup closed time as a proxy for the row instead. 默认持续时间为 0 分钟。The default duration is 0 minutes. 一旦增量行组中累积了 100 万行,并且该行组标记为已关闭,就会将行迁移到列存储。A row is migrated to columnar storage once 1 million rows have been accumulated in delta rowgroup and it has been marked closed.

DATA_COMPRESSIONDATA_COMPRESSION

为指定的表、分区号或分区范围指定数据压缩选项。Specifies the data compression option for the specified table, partition number, or range of partitions. 仅适用于列存储索引,包括非聚集列存储索引和聚集列存储索引。Applies only to columnstore indexes, including both nonclustered columnstore and clustered columnstore indexes. 选项如下所示:The options are as follows:

  • COLUMNSTORE - 默认值,它指定使用性能最高的列存储压缩进行压缩。COLUMNSTORE - the default and specifies to compress with the most performant columnstore compression. 这是典型选择。This is the typical choice.
  • COLUMNSTORE_ARCHIVE - COLUMNSTORE_ARCHIVE 将表或分区进一步压缩为更小的大小。COLUMNSTORE_ARCHIVE - COLUMNSTORE_ARCHIVE further compresses the table or partition to a smaller size. 这可用于存档,或者用于要求更小存储大小并且可以付出更多时间来进行存储和检索的其他情形。This can be used for archival, or for other situations that require a smaller storage size and can afford more time for storage and retrieval.

有关压缩的详细信息,请参阅数据压缩For more information about compression, see Data Compression.

WHERE <filter_expression> [ AND <filter_expression> ]WHERE <filter_expression> [ AND <filter_expression> ]

调用一个筛选器谓词,它指定哪些行包含在索引中。Called a filter predicate, this specifies which rows to include in the index. SQL ServerSQL Server 对筛选索引中的数据行创建筛选统计信息。creates filtered statistics on the data rows in the filtered index.

该筛选器谓词使用简单的比较逻辑。The filter predicate uses simple comparison logic. 比较运算符不允许使用 NULL 文本的比较。Comparisons using NULL literals are not allowed with the comparison operators. 请改用 IS NULL 和 IS NOT NULL 运算符。Use the IS NULL and IS NOT NULL operators instead.

下面是一些 Production.BillOfMaterials 表筛选谓词示例:Here are some examples of filter predicates for the Production.BillOfMaterials table:
WHERE StartDate > '20000101' AND EndDate <= '20000630'
WHERE ComponentID IN (533, 324, 753)
WHERE StartDate IN ('20000404', '20000905') AND EndDate IS NOT NULL

有关筛选索引的指南,请参阅创建筛选索引For guidance on filtered indexes, see Create Filtered Indexes.

ON 选项ON options

这些选项指定创建该索引时所在的文件组。These options specify the filegroups on which the index is created.

partition_scheme_name ( column_name )partition_scheme_name ( column_name )
指定分区方案,该方案定义要将已分区索引的分区映射到的文件组。Specifies the partition scheme that defines the filegroups onto which the partitions of a partitioned index is mapped. 必须通过执行 CREATE PARTITION SCHEME 使数据库中存在该分区方案。The partition scheme must exist within the database by executing CREATE PARTITION SCHEME. column_name 指定对已分区索引进行分区所依据的列。column_name specifies the column against which a partitioned index is partitioned. 该列必须与 partition_scheme_name 使用的分区函数参数的数据类型、长度和精度相匹配 。This column must match the data type, length, and precision of the argument of the partition function that partition_scheme_name is using. column_name 不限于索引定义中的列 。column_name is not restricted to the columns in the index definition. 在对列存储索引进行分区时,如果尚未指定分区依据列,则数据库引擎Database Engine会添加分区依据列作为索引列。When partitioning a columnstore index, 数据库引擎Database Engine adds the partitioning column as a column of the index, if it is not already specified.
如果未指定 partition_scheme_name 或 filegroup 且该表已分区,则索引会与基础表使用相同分区依据列并被放入同一分区方案中 。If partition_scheme_name or filegroup is not specified and the table is partitioned, the index is placed in the same partition scheme, using the same partitioning column, as the underlying table.
分区表的列存储索引必须实现分区对齐。A columnstore index on a partitioned table must be partition aligned.
有关分区索引的详细信息,请参阅已分区表和已分区索引For more information about partitioning indexes, see Partitioned Tables and Indexes.

filegroup_name filegroup_name
指定要对其创建索引的文件组名称。Specifies a filegroup name on which to create the index. 如果未指定 filegroup_name 并且该表未分区,则索引与基础表使用相同的文件组。If filegroup_name is not specified and the table is not partitioned, the index uses the same filegroup as the underlying table. 该文件组必须已存在。The filegroup must already exist.

"default" " default "
为默认文件组创建指定索引。Creates the specified index on the default filegroup.

在此上下文中,“default”一词不是关键字。The term default, in this context, is not a keyword. 而是默认文件组的标识符,并且必须进行分隔,如 ON "default" 或 ON [default] 中所示 。It is an identifier for the default filegroup and must be delimited, as in ON " default " or ON [ default ]. 如果指定了 "default",则当前会话的 QUOTED_IDENTIFIER 选项必须为 ON。If "default" is specified, the QUOTED_IDENTIFIER option must be ON for the current session. 这是默认设置。This is the default setting. 有关详细信息,请参阅 SET QUOTED_IDENTIFIER (Transact-SQL)For more information, see SET QUOTED_IDENTIFIER (Transact-SQL).

权限Permissions

需要对表的 ALTER 权限。Requires ALTER permission on the table.

一般备注General Remarks

可以为临时表创建列存储索引。A columnstore index can be created on a temporary table. 在删除表或结束会话时,也将删除索引。When the table is dropped or the session ends, the index is also dropped.

筛选索引Filtered Indexes

筛选索引是一种经过优化的非聚集索引,适用于从表中选择少数行的查询。A filtered index is an optimized nonclustered index, suited for queries that select a small percentage of rows from a table. 筛选索引使用筛选谓词对表中的部分数据进行索引。It uses a filter predicate to index a portion of the data in the table. 设计良好的筛选索引可以提高查询性能,降低存储成本和维护成本。A well-designed filtered index can improve query performance, reduce storage costs, and reduce maintenance costs.

筛选索引所需的 SET 选项Required SET Options for Filtered Indexes

如果下列任何条件成立,则需要“必需的值”列中的 SET 选项:The SET options in the Required Value column are required whenever any of the following conditions occur:

  • 创建筛选索引。Create a filtered index.

  • INSERT、UPDATE、DELETE 或 MERGE 操作修改筛选索引中的数据。INSERT, UPDATE, DELETE, or MERGE operation modifies the data in a filtered index.

  • 查询优化器使用该筛选索引生成查询计划。The filtered index is used by the query optimizer to produce the query plan.

    SET 选项SET options 必需的值Required value 默认服务器值Default server value 默认Default

    OLE DB 和 ODBC 值OLE DB and ODBC value
    默认Default

    DB-Library 值DB-Library value
    ANSI_NULLSANSI_NULLS ONON ONON ONON OFFOFF
    ANSI_PADDINGANSI_PADDING ONON ONON ONON OFFOFF
    ANSI_WARNINGS*ANSI_WARNINGS* ONON ONON ONON OFFOFF
    ARITHABORTARITHABORT ONON ONON OFFOFF OFFOFF
    CONCAT_NULL_YIELDS_NULLCONCAT_NULL_YIELDS_NULL ONON ONON ONON OFFOFF
    NUMERIC_ROUNDABORTNUMERIC_ROUNDABORT OFFOFF OFFOFF OFFOFF OFFOFF
    QUOTED_IDENTIFIERQUOTED_IDENTIFIER ONON ONON ONON OFFOFF

    *当数据库兼容性级别设置为 90 或更高时,如果将 ANSI_WARNINGS 设置为 ON,则将使 ARITHABORT 隐式设置为 ON。*Setting ANSI_WARNINGS to ON implicitly sets ARITHABORT to ON when the database compatibility level is set to 90 or higher. 如果数据库兼容性级别设置为 80 或更低,则必须将 ARITHABORT 选项显式设置为 ON。If the database compatibility level is set to 80 or earlier, the ARITHABORT option must explicitly be set to ON.

如果 SET 选项不正确,则可能会出现以下情况:If the SET options are incorrect, the following conditions can occur:

  • 不会创建筛选索引。The filtered index is not created.

  • 数据库引擎Database Engine生成错误并回滚对索引中的数据进行更改的 INSERT、UPDATE、DELETE 或 MERGE 语句。The 数据库引擎Database Engine generates an error and rolls back INSERT, UPDATE, DELETE, or MERGE statements that change data in the index.

  • 查询优化器不考虑任何 Transact-SQL 语句的执行计划中的索引。Query optimizer does not consider the index in the execution plan for any Transact-SQL statements.

有关筛选索引的详细信息,请参阅创建筛选索引For more information about Filtered Indexes, see Create Filtered Indexes.

限制和局限Limitations and Restrictions

列存储索引中的每一列都必须是以下常见业务数据类型之一:Each column in a columnstore index must be of one of the following common business data types:

  • datetimeoffset [ ( n ) ] datetimeoffset [ ( n ) ]
  • datetime2 [ ( n ) ] datetime2 [ ( n ) ]
  • DATETIMEdatetime
  • smalldatetimesmalldatetime
  • 日期date
  • time [ ( n ) ] time [ ( n ) ]
  • float [ ( n ) ] float [ ( n ) ]
  • real [ ( n ) ] real [ ( n ) ]
  • decimal [ ( precision [ , scale ] ) ]decimal [ ( precision [ , scale ] ) ]
  • numeric [ ( precision [ , scale ] ) ]numeric [ ( precision [ , scale ] ) ]
  • moneymoney
  • SMALLMONEYsmallmoney
  • BIGINTbigint
  • INTint
  • smallintsmallint
  • TINYINTtinyint
  • bitbit
  • nvarchar [ ( n ) ] nvarchar [ ( n ) ]
  • nvarchar(max) (适用于 SQL Server 2017 (14.x)SQL Server 2017 (14.x) 和高级层、标准层(S3 及更高),以及所有 VCore 产品/服务层,仅限聚集列存储索引)nvarchar(max) (Applies to SQL Server 2017 (14.x)SQL Server 2017 (14.x) and Premium tier, Standard tier (S3 and above), and all VCore offerings tiers, in clustered columnstore indexes only)
  • nchar [ ( n ) ] nchar [ ( n ) ]
  • varchar [ ( n ) ] varchar [ ( n ) ]
  • varchar(max) (适用于 SQL Server 2017 (14.x)SQL Server 2017 (14.x) 和高级层、标准层(S3 及更高),以及所有 VCore 产品/服务层,仅限聚集列存储索引)varchar(max) (Applies to SQL Server 2017 (14.x)SQL Server 2017 (14.x) and Premium tier, Standard tier (S3 and above), and all VCore offerings tiers, in clustered columnstore indexes only)
  • char [ ( n ) ] char [ ( n ) ]
  • varbinary [ ( n ) ] varbinary [ ( n ) ]
  • varbinary(max) (适用于 SQL Server 2017 (14.x)SQL Server 2017 (14.x) 和高级层、标准层(S3 及更高)的 Azure SQL 数据库,以及所有 VCore 产品/服务层,仅限聚集列存储索引)varbinary (max) (Applies to SQL Server 2017 (14.x)SQL Server 2017 (14.x) and Azure SQL Database at Premium tier, Standard tier (S3 and above), and all VCore offerings tiers, in clustered columnstore indexes only)
  • binary [ ( n ) ] binary [ ( n ) ]
  • uniqueidentifier(适用于 SQL Server 2014 (12.x)SQL Server 2014 (12.x) 及更高版本)uniqueidentifier (Applies to SQL Server 2014 (12.x)SQL Server 2014 (12.x) and later)

如果基础表中包含的某一列的数据类型不受列存储索引支持,则必须从非聚集列存储索引中省略该列。If the underlying table has a column of a data type that is not supported for columnstore indexes, you must omit that column from the nonclustered columnstore index.

使用以下任何数据类型的列都不能包括在列存储索引中:Columns that use any of the following data types cannot be included in a columnstore index:

  • ntext、text 和 imagentext, text, and image
  • nvarchar(max)、varchar(max) 和 varbinary(max)(适用于 SQL Server 2016 (13.x)SQL Server 2016 (13.x) 和早期版本,以及非聚集列存储索引)nvarchar(max), varchar(max), and varbinary(max) (Applies to SQL Server 2016 (13.x)SQL Server 2016 (13.x) and prior versions, and nonclustered columnstore indexes)
  • rowversion(和 timestamp)rowversion (and timestamp)
  • sql_variantsql_variant
  • CLR 类型(hierarchyid 和空间类型)CLR types (hierarchyid and spatial types)
  • xmlxml
  • uniqueidentifier(适用于 SQL Server 2012 (11.x)SQL Server 2012 (11.x)uniqueidentifier (Applies to SQL Server 2012 (11.x)SQL Server 2012 (11.x))

非聚集列存储索引:Nonclustered columnstore indexes:

  • 包含的列数不能超过 1024。Cannot have more than 1024 columns.
  • 无法创建为基于约束的索引。Cannot be created as a constraint-based index. 对于具有列存储索引的表,可以具有唯一约束、主键约束和外键约束。It is possible to have unique constraints, primary key constraints, and foreign key constraints on a table with a columnstore index. 总是通过行存储索引强制执行约束。Constraints are always enforced with a row-store index. 无法使用列存储(群集或非群集)索引强制执行约束。Constraints cannot be enforced with a columnstore (clustered or nonclustered) index.
  • 不能包含稀疏列。Cannot include a sparse column.
  • 不能使用 ALTER INDEX 语句进行更改。Cannot be changed by using the ALTER INDEX statement. 若要更改非聚集索引,必须先删除该列存储索引,然后重新创建它。To change the nonclustered index, you must drop and re-create the columnstore index instead. 可以使用 ALTER INDEX 禁用并重新生成列存储索引。You can use ALTER INDEX to disable and rebuild a columnstore index.
  • 不能使用 INCLUDE 关键字创建。Cannot be created by using the INCLUDE keyword.
  • 不能包括用来对索引排序的 ASCDESC 关键字。Cannot include the ASC or DESC keywords for sorting the index. 根据压缩算法对列存储索引排序。Columnstore indexes are ordered according to the compression algorithms. 排序将抵销许多性能优势。Sorting would eliminate many of the performance benefits.
  • 不能在非聚集列存储索引中包含 nvarchar(max)、varchar(max) 和 varbinary(max) 类型的大型对象 (LOB) 列。Cannot include large object (LOB) columns of type nvarchar(max), varchar(max), and varbinary(max) in nonclustered column store indexes. 仅从 SQL Server 2017 (14.x)SQL Server 2017 (14.x) 版本开始以及在高级层、标准层(S3 及更高)以及所有 VCore 产品/服务层配置的 Azure SQL 数据库中,聚集列存储索引才支持 LOB 类型。Only clustered columnstore indexes support LOB types, beginning in SQL Server 2017 (14.x)SQL Server 2017 (14.x) version and Azure SQL Database configured at Premium tier, Standard tier (S3 and above), and all VCore offerings tiers tier. 请注意,以前的版本不管是在聚集列存储索引还是非聚集列存储索引中都不支持 LOB 类型。Note, prior versions do not support LOB types in clustered and nonclustered columnstore indexes.

备注

SQL Server 2016 (13.x)SQL Server 2016 (13.x) 开始,可以在索引视图上创建非聚集列存储索引。Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x), you can create a nonclustered columnstore index on an indexed view.

列存储索引不能与以下功能结合使用:Columnstore indexes cannot be combined with the following features:

  • 计算列。Computed columns. 从 SQL Server 2017 开始,聚集列存储索引可以包含非持久化计算列。Starting with SQL Server 2017, a clustered columnstore index can contain a non-persisted computed column. 但是,在 SQL Server 2017 中,聚集列存储索引不能包含持久化计算列,并且你不能对计算列创建非聚集索引。However, in SQL Server 2017, clustered columnstore indexes cannot contain persisted computed columns, and you cannot created nonclustered indexes on computed columns.
  • 页和行压缩以及 vardecimal 存储格式(列存储索引已采用不同格式压缩)。Page and row compression, and vardecimal storage format (A columnstore index is already compressed in a different format.)
  • 复制Replication
  • 文件流Filestream

不能在具有聚集列存储索引的表中使用游标或触发器。You cannot use cursors or triggers on a table with a clustered columnstore index. 此限制不适用于非聚集列存储索引;可以在具有非聚集列存储索引的表中使用游标和触发器。This restriction does not apply to nonclustered columnstore indexes; you can use cursors and triggers on a table with a nonclustered columnstore index.

SQL Server 2014 (12.x)SQL Server 2014 (12.x) 特定限制SQL Server 2014 (12.x)SQL Server 2014 (12.x) specific limitations
这些限制仅适用于 SQL Server 2014 (12.x)SQL Server 2014 (12.x)These limitations apply only to SQL Server 2014 (12.x)SQL Server 2014 (12.x). 在此版本中,我们引入了可更新的聚集列存储索引。In this release, we introduced updateable clustered columnstore indexes. 非聚集列存储索引仍为只读。Nonclustered columnstore indexes were still read-only.

  • 更改跟踪。Change tracking. 不能将更改跟踪与列存储索引配合使用。You cannot use change tracking with columnstore indexes.
  • 变更数据捕获。Change data capture. 不能对非聚集列存储索引 (NCCI) 使用变更数据捕获,因为这类索引是只读的。You cannot use change data capture for nonclustered columnstore index (NCCI) because they are read-only. 它适用于聚集列存储索引 (CCI)。It does work for clustered columnstore indexes (CCI).
  • 可读辅助副本。Readable secondary. 不能通过 Always OnReadable 可用性组的可读辅助副本访问聚集列存储索引 (CCI)。You cannot access a clustered columnstore index (CCI) from a readable secondary of an Always OnReadable availability group. 可以通过可读辅助副本访问非聚集列存储索引 (NCCI)。You can access a nonclustered columnstore index (NCCI) from a readable secondary.
  • 多重活动结果集 (MARS)。Multiple Active Result Sets (MARS). SQL Server 2014 (12.x)SQL Server 2014 (12.x) 使用 MARS 对包含列存储索引的表执行只读连接。uses MARS for read-only connections to tables with a columnstore index. 不过,SQL Server 2014 (12.x)SQL Server 2014 (12.x) 不支持使用 MARS 对包含列存储索引的表执行并发数据操作语言 (DML) 操作。However, SQL Server 2014 (12.x)SQL Server 2014 (12.x) does not support MARS for concurrent data manipulation language (DML) operations on a table with a columnstore index. 如果发生这种情况,SQL ServerSQL Server 会终止连接,并中止事务。When this occurs, SQL ServerSQL Server terminates the connections and aborts the transactions.
  • 无法在视图或索引视图上创建非聚集列存储索引。Nonclustered columnstore indexes cannot be created on a view or indexed view.

有关列存储索引的性能优势和限制的信息,请参阅列存储索引概述For information about the performance benefits and limitations of columnstore indexes, see Columnstore Indexes Overview.

元数据Metadata

列存储索引中的所有列在元数据中作为包含性列存储。All of the columns in a columnstore index are stored in the metadata as included columns. 列存储索引中没有任何键列。The columnstore index does not have key columns. 这些系统视图提供有关列存储索引的信息。These system views provide information about columnstore indexes.

将行存储表转换为列存储的示例Examples for converting a rowstore table to columnstore

A.A. 将堆转换为聚集列存储索引Convert a heap to a clustered columnstore index

此示例将一个表作为堆创建,然后将其转换为名为 cci_Simple 的聚集列存储索引。This example creates a table as a heap and then converts it to a clustered columnstore index named cci_Simple. 这会将整个表的存储从行存储转换为列存储。This changes the storage for the entire table from rowstore to columnstore.

CREATE TABLE 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 SimpleTable;  
GO  

B.B. 将聚集索引转换为具有相同名称的聚集列存储索引。Convert a clustered index to a clustered columnstore index with the same name.

此示例创建一个具有聚集索引的表,然后演示将该聚集索引转换为聚集列存储索引的语法。This example creates a table with clustered index, and then demonstrates the syntax of converting the clustered index to a clustered columnstore index. 这会将整个表的存储从行存储转换为列存储。This changes the storage for the entire table from rowstore to columnstore.

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 CLUSTERED COLUMNSTORE INDEX cl_simple ON SimpleTable  
WITH (DROP_EXISTING = ON);  
GO  

C.C. 将行存储表转换为列存储索引时处理非聚集索引。Handle nonclustered indexes when converting a rowstore table to a columnstore index.

此示例展示如何在将行存储表转换为列存储索引时处理非聚集索引。This example shows how to handle nonclustered indexes when converting a rowstore table to a columnstore index. 实际上,从 SQL Server 2016 (13.x)SQL Server 2016 (13.x) 开始,无需执行特别的操作;SQL ServerSQL Server 会在新的聚集列存储索引上自动定义并重新生成非聚集索引。Actually, beginning with SQL Server 2016 (13.x)SQL Server 2016 (13.x) no special action is required; SQL ServerSQL Server automatically defines and rebuilds the nonclustered indexes on the new clustered columnstore index.

如果要删除非聚集索引,请在创建列存储索引之前使用 DROP INDEX 语句。If you want to drop the nonclustered indexes, use the DROP INDEX statement prior to creating the columnstore index. DROP EXISTING 选项仅删除正在转换的聚集索引。The DROP EXISTING option only drops the clustered index that is being converted. 它不会删除非聚集索引。It does not drop the nonclustered indexes.

SQL Server 2012 (11.x)SQL Server 2012 (11.x)SQL Server 2014 (12.x)SQL Server 2014 (12.x) 中,无法在列存储索引上创建非聚集索引。In SQL Server 2012 (11.x)SQL Server 2012 (11.x) and SQL Server 2014 (12.x)SQL Server 2014 (12.x), you could not create a nonclustered index on a columnstore index. 此示例展示在早期版本中,如何在创建列存储索引之前删除非聚集索引。This example shows how in previous releases you need to drop the nonclustered indexes before creating the columnstore index.

--Create the table for use with this example.  
CREATE TABLE 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 SimpleTable (OrderDateKey);  
CREATE INDEX nc2_simple ON SimpleTable (DueDateKey);   
GO  
  
--SQL Server 2012 and SQL Server 2014: you need to drop the nonclustered indexes  
--in order to create the columnstore index.   
  
DROP INDEX SimpleTable.nc1_simple;  
DROP INDEX SimpleTable.nc2_simple;  
  
--Convert the rowstore table to a columnstore index.  
CREATE CLUSTERED COLUMNSTORE INDEX cci_simple ON SimpleTable;   
GO  

D.D. 将大型事实表从行存储转换为列存储方式Convert a large fact table from rowstore to columnstore

此示例说明如何将大型事实表从行存储表转换为列存储表。This example explains how to convert a large fact table from a rowstore table to a columnstore table.

将行存储表转换为列存储表To convert a rowstore table to a columnstore table.

  1. 首先,创建要在此示例中使用的一个较小的表。First, create a small table to use in this example.

    --Create a rowstore table with a clustered index and a nonclustered index.  
    CREATE TABLE MyFactTable (  
        ProductKey [int] NOT NULL,  
        OrderDateKey [int] NOT NULL,  
         DueDateKey [int] NOT NULL,  
         ShipDateKey [int] NOT NULL )  
    )  
    WITH (  
        CLUSTERED INDEX ( ProductKey )  
    );  
    
    --Add a nonclustered index.  
    CREATE INDEX my_index ON MyFactTable ( ProductKey, OrderDateKey );  
    
  2. 从行存储表中删除所有非聚集索引。Drop all nonclustered indexes from the rowstore table.

    --Drop all nonclustered indexes  
    DROP INDEX my_index ON MyFactTable;  
    
  3. 删除聚集索引。Drop the clustered index.

    • 仅在您要在将它转换为聚集列存储索引时指定索引的新名称的情况下才这样做。Do this only if you want to specify a new name for the index when it is converted to a clustered columnstore index. 如果不删除聚集索引,新的聚集列存储索引将具有相同的名称。If you do not drop the clustered index, the new clustered columnstore index has the same name.

      备注

      如果您使用自己的名称,索引的名称可能更易于记住。The name of the index might be easier to remember if you use your own name. 所有行存储格式的聚集索引均使用以下默认名称:“ClusteredIndex_<GUID>”。All rowstore clustered indexes use the default name which is 'ClusteredIndex_<GUID>'.

    --Process for dropping a clustered index.  
    --First, look up the name of the clustered rowstore index.  
    --Clustered rowstore indexes always use the DEFAULT name 'ClusteredIndex_<GUID>'.  
    SELECT i.name   
    FROM sys.indexes i   
    JOIN sys.tables t  
    ON ( i.type_desc = 'CLUSTERED' ) WHERE t.name = 'MyFactTable';  
    
    --Drop the clustered rowstore index.  
    DROP INDEX ClusteredIndex_d473567f7ea04d7aafcac5364c241e09 ON MyDimTable;  
    
  4. 将行存储表转换为具有聚集列存储索引的列存储表。Convert the rowstore table to a columnstore table with a clustered columnstore index.

    --Option 1: Convert to columnstore and name the new clustered columnstore index MyCCI.  
    CREATE CLUSTERED COLUMNSTORE INDEX MyCCI ON MyFactTable;  
    
    --Option 2: Convert to columnstore and use the rowstore clustered   
    --index name for the columnstore clustered index name.  
    --First, look up the name of the clustered rowstore index.  
    SELECT i.name   
    FROM sys.indexes i  
    JOIN sys.tables t   
    ON ( i.type_desc = 'CLUSTERED' )  
    WHERE t.name = 'MyFactTable';  
    
    --Second, create the clustered columnstore index and   
    --Replace ClusteredIndex_d473567f7ea04d7aafcac5364c241e09  
    --with the name of your clustered index.  
    CREATE CLUSTERED COLUMNSTORE INDEX   
    ClusteredIndex_d473567f7ea04d7aafcac5364c241e09  
     ON MyFactTable  
    WITH DROP_EXISTING = ON;  
    

E.E. 将列存储表转换为具有聚集索引的行存储表Convert a columnstore table to a rowstore table with a clustered index

要将列存储表转换为具有聚集索引的行存储表,请使用带 DROP_EXISTING 选项的 CREATE INDEX 语句。To convert a columnstore table to a rowstore table with a clustered index, use the CREATE INDEX statement with the DROP_EXISTING option.

CREATE CLUSTERED INDEX ci_MyTable   
ON MyFactTable  
WITH ( DROP EXISTING = ON );  

F.F. 将列存储表转换为行存储堆Convert a columnstore table to a rowstore heap

要将列存储表转换为行存储堆,只需删除聚集列存储索引即可。To convert a columnstore table to a rowstore heap, simply drop the clustered columnstore index.

DROP INDEX MyCCI   
ON MyFactTable;  

G.G. 重新生成整个聚集列存储索引以进行碎片整理Defragment by rebuilding the entire clustered columnstore index

适用范围:SQL Server 2014 (12.x)SQL Server 2014 (12.x)Applies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x)

有两种方法可以重新生成完整的聚集列存储索引。There are two ways to rebuild the full clustered columnstore index. 可以使用 CREATE CLUSTERED COLUMNSTORE INDEX,或使用 ALTER INDEX (Transact-SQL) 和 REBUILD 选项。You can use CREATE CLUSTERED COLUMNSTORE INDEX, or ALTER INDEX (Transact-SQL) and the REBUILD option. 这两种方法可以得到相同的结果。Both methods achieve the same results.

备注

SQL Server 2016 (13.x)SQL Server 2016 (13.x) 起,改用 ALTER INDEX...REORGANIZE,而不使用此示例中所述的方法来重新生成。Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x), use ALTER INDEX...REORGANIZE instead of rebuilding with the methods described in this example.

--Determine the Clustered Columnstore Index name of MyDimTable.  
SELECT i.object_id, i.name, t.object_id, t.name   
FROM sys.indexes i   
JOIN sys.tables t  
ON (i.type_desc = 'CLUSTERED COLUMNSTORE')  
WHERE t.name = 'RowstoreDimTable';  
  
--Rebuild the entire index by using CREATE CLUSTERED INDEX.  
CREATE CLUSTERED COLUMNSTORE INDEX my_CCI   
ON MyFactTable  
WITH ( DROP_EXISTING = ON );  
  
--Rebuild the entire index by using ALTER INDEX and the REBUILD option.  
ALTER INDEX my_CCI  
ON MyFactTable  
REBUILD PARTITION = ALL  
WITH ( DROP_EXISTING = ON );  

非聚集列存储索引示例Examples for nonclustered columnstore indexes

A.A. 对行存储表创建列存储索引作为辅助索引Create a columnstore index as a secondary index on a rowstore table

此示例会对行存储表创建非聚集列存储索引。This example creates a nonclustered columnstore index on a rowstore table. 在这种情况下只能创建一个列存储索引。Only one columnstore index can be created in this situation. 列存储索引需要额外的存储空间,因为它包含行存储表中数据的副本。The columnstore index requires extra storage since it contains a copy of the data in the rowstore table. 此示例会创建一个简单表和聚集索引,然后演示创建非聚集列存储索引的语法。This example creates a simple table and a clustered index, and then demonstrates the syntax of creating a nonclustered columnstore index.

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.B. 使用所有选项创建简单的非聚集列存储索引Create a simple nonclustered columnstore index using all options

下面的示例演示了通过使用所有选项创建非聚集列存储索引的语法。The following example demonstrates the syntax of creating a nonclustered columnstore index by using all options.

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

有关使用已分区表的更复杂示例,请参阅列存储索引概述For a more complex example using partitioned tables, see Columnstore Indexes Overview.

C.C. 使用筛选谓词创建非聚集列存储索引Create a nonclustered columnstore index with a filtered predicate

以下示例会对 AdventureWorks2012AdventureWorks2012 数据库中的 Production.BillOfMaterials 表创建已筛选的非聚集列存储索引。The following example creates a filtered nonclustered columnstore index on the Production.BillOfMaterials table in the AdventureWorks2012AdventureWorks2012 database. 筛选谓词可包含那些不是筛选索引中的键列的列。The filter predicate can include columns that are not key columns in the filtered index. 本示例中的谓词将仅选择其中的 EndDate 为非 NULL 的行。The predicate in this example selects only the rows where EndDate is non-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.D. 更改非聚集列存储索引中的数据Change the data in a nonclustered columnstore index

适用范围:SQL Server 2012 (11.x)SQL Server 2012 (11.x)SQL Server 2014 (12.x)SQL Server 2014 (12.x)Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2014 (12.x)SQL Server 2014 (12.x).

在您在表上创建非聚集列存储索引后,不能直接在该表中修改数据。Once you create a nonclustered columnstore index on a table, you cannot directly modify the data in that table. 具有 INSERT、UPDATE、DELETE 或 MERGE 的查询会失败并且返回错误消息。A query with INSERT, UPDATE, DELETE, or MERGE fails and returns an error message. 若要添加或修改表中的数据,可以执行以下操作之一:To add or modify the data in the table, you can do one of the following:

  • 禁用或删除列存储索引。Disable or drop the columnstore index. 然后可以更新表中的数据。You can then update the data in the table. 如果禁用列存储索引,则可以在完成数据更新后重新生成列存储索引。If you disable the columnstore index, you can rebuild the columnstore index when you finish updating the data. 例如,For example,

    ALTER INDEX mycolumnstoreindex ON mytable DISABLE;  
    -- update mytable --  
    ALTER INDEX mycolumnstoreindex on mytable REBUILD  
    
  • 将数据加载到不含列存储索引的临时表中。Load data into a staging table that does not have a columnstore index. 在临时表上生成列存储索引。Build a columnstore index on the staging table. 将临时表切换到主表的一个空分区中。Switch the staging table into an empty partition of the main table.

  • 将分区从具有列存储索引的表切换到一个空的临时表中。Switch a partition from the table with the columnstore index into an empty staging table. 如果在临时表上有某个列存储索引,则禁用该列存储索引。If there is a columnstore index on the staging table, disable the columnstore index. 执行任何更新。Perform any updates. 生成(或重新生成)列存储索引。Build (or rebuild) the columnstore index. 将临时表切换回主表的(现在为空的)分区中。Switch the staging table back into the (now empty) partition of the main table.

示例:Azure SQL 数据仓库Azure SQL Data Warehouse并行数据仓库Parallel Data WarehouseExamples: Azure SQL 数据仓库Azure SQL Data Warehouse and 并行数据仓库Parallel Data Warehouse

A.A. 将聚集索引更改为聚集列存储索引Change a clustered index to a clustered columnstore index

通过使用 DROP_EXISTING = ON 的 CREATE CLUSTERED COLUMNSTORE INDEX 语句,可以:By using the CREATE CLUSTERED COLUMNSTORE INDEX statement with DROP_EXISTING = ON, you can:

  • 将聚集索引更改为聚集列存储索引。Change a clustered index into a clustered columnstore index.

  • 重新生成聚集列存储索引。Rebuild a clustered columnstore index.

此示例将 xDimProduct 表创建为具有聚集索引的行存储表,然后使用 CREATE CLUSTERED COLUMNSTORE INDEX 将该表从行存储表更改为列存储表。This example creates the xDimProduct table as a rowstore table with a clustered index, and then uses CREATE CLUSTERED COLUMNSTORE INDEX to change the table from a rowstore table to a columnstore table.

-- 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;  
  
--Change the existing clustered index   
--to a clustered columnstore index with the same name.  
--Look up the name of the index before running this statement.  
CREATE CLUSTERED COLUMNSTORE INDEX <index_name>   
ON xdimProduct   
WITH ( DROP_EXISTING = ON );  

B.B. 重新生成聚集列存储索引Rebuild a clustered columnstore index

基于上一示例,此示例使用 CREATE CLUSTERED COLUMNSTORE INDEX 重新生成名为 cci_xDimProduct 的现有聚集列存储索引。Building on the previous example, this example uses CREATE CLUSTERED COLUMNSTORE INDEX to rebuild the existing clustered columnstore index called cci_xDimProduct.

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

C.C. 更改聚集列存储索引的名称Change the name of a clustered columnstore index

若要更改聚集列存储索引的名称,请删除现有的聚集列存储索引,然后使用新名称重新创建索引。To change the name of a clustered columnstore index, drop the existing clustered columnstore index, and then recreate the index with a new name.

建议仅对小型表或空表执行此操作。We recommend only doing this operation with a small table or an empty table. 删除大型聚集列存储索引并使用其他名称重新生成需要很长时间。It takes a long time to drop a large clustered columnstore index and rebuild with a different name.

通过使用上一示例中的 cci_xDimProduct 聚集列存储索引,此示例将删除 cci_xDimProduct 聚集列存储索引,然后使用名称 mycci_xDimProduct 重新创建聚集列存储索引。Using the cci_xDimProduct clustered columnstore index from the previous example, this example drops the cci_xDimProduct clustered columnstore index and then recreates the clustered columnstore index with the name 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.D. 将列存储表转换为具有聚集索引的行存储表Convert a columnstore table to a rowstore table with a clustered index

可能会出现想删除聚集列存储索引并创建聚集索引的情况。There might be a situation for which you want to drop a clustered columnstore index and create a clustered index. 这会以行存储格式存储表。This stores the table in rowstore format. 此示例会将列存储表转换为具有同名聚集索引的行存储表。This example converts a columnstore table to a rowstore table with a clustered index with the same name. 不会丢失任何数据。None of the data is lost. 所有数据都转到行存储表中,列出的列成为聚集索引中的键列。All data goes to the rowstore table and the columns listed becomes the key columns in the clustered index.

--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.E. 将列存储表转换回行存储堆Convert a columnstore table back to a rowstore heap

使用 DROP INDEX (SQL Server PDW) 删除聚集列存储索引并将表转换为行存储堆。Use DROP INDEX (SQL Server PDW) to drop the clustered columnstore index and convert the table to a rowstore heap. 此示例会将 cci_xDimProduct 表转换为行存储堆。This example converts the cci_xDimProduct table to a rowstore heap. 可继续分配该表,但将其存储为堆。The table continues to be distributed, but is stored as a heap.

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

F.F. 在不具有索引的表上创建有序聚集列存储索引Create an ordered clustered columnstore index on a table with no index

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

G.G. 将聚集列存储索引转换为有序聚集列存储索引Convert a clustered columnstore index to an ordered clustered columnstore index

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

H.H. 向有序聚集列存储索引的排序添加列Add a column to the ordering of an ordered clustered columnstore index

-- The original ordered clustered columnstore index was ordered on SHIPDATE column only.  Add PRODUCTKEY column to the ordering.
CREATE CLUSTERED COLUMNSTORE INDEX cci ON Sales.OrderLines
ORDER ( SHIPDATE, PRODUCTKEY );
WITH (DROP_EXISTING = ON)

I.I. 更改有序列的序号Change the ordinal of ordered columns

-- The original ordered clustered columnstore index was ordered on SHIPDATE, PRODUCTKEY.  Change the ordering to PRODUCTKEY, SHIPDATE.  
CREATE CLUSTERED COLUMNSTORE INDEX cci ON Sales.OrderLines
ORDER ( PRODUCTKEY,SHIPDATE );
WITH (DROP_EXISTING = ON)