CREATE COLUMNSTORE INDEX (Transact-SQL)

適用於：Microsoft Fabric 中的 SQL Server Azure SQL 資料庫 Azure SQL 受控執行個體 Azure Synapse Analytics Analytics Platform System （PDW） SQL Database

將資料列存放區資料表轉換為叢集資料行存放區索引，或建立非叢集資料行存放區索引。 有效率地對 OLTP 工作負載執行即時作業分析，或是改善資料倉儲工作負載的資料壓縮和查詢效能，請使用資料行存放區索引。

請前往資料行存放區索引的新功能，了解這項功能的最新改善項目。

• SQL Server 2022 (16.x) 引進排序的叢集資料行存放區索引。 如需詳細資訊，請參閱 CREATE COLUMNSTORE INDEX (機器翻譯)。 如需已排序的數據行存放區索引可用性，請參閱 數據行存放區索引：概觀。

• 從 SQL Server 2016 (13.x) 開始，您可以建立資料表作為叢集資料行存放區索引。 您再也不用先建立資料列存放區資料表，然後將它轉換成叢集資料行存放區索引。

• 如需資料行存放區索引設計指導方針的資訊，請參閱資料行存放區索引 - 設計指導 (機器翻譯)。

Transact-SQL 語法慣例

語法

SQL Server 和 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 }
    [ 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 ] )
    [ ORDER (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、平行處理資料倉儲、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
[;]

引數

部分選項無法在所有資料庫引擎版本上使用。 下表說明當在 CLUSTERED COLUMNSTORE 和 NONCLUSTERED COLUMNSTORE 索引中引入該選項時，所顯示的版本：

選項	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 子句	N/A	SQL Server 2016 (13.x)

所有選項皆可在 Azure SQL Database 中使用。

CREATE CLUSTERED COLUMNSTORE INDEX

建立叢集資料行存放區索引，由資料行將所有資料壓縮並儲存。 索引會包括資料表中的所有資料行，而且將儲存整個資料表。 如果現有的數據表是堆積或叢集索引，則會轉換成叢集數據行存放區索引。 如果資料表已儲存為叢集資料行存放區索引，則會卸除並重新編製現有的索引。

index_name

指定新索引的名稱。

如果資料表已經有叢集資料行存放區索引，則您可以指定與現有索引相同的名稱，或您可以使用 DROP EXISTING 選項來指定新名稱。

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

指定要儲存為叢集資料行存放區索引之資料表的單部分、兩部分或三部分名稱。 如果資料表是堆積或有叢集索引，則該資料表會從資料列存放區轉換成資料行存放區。 如果資料表已經是資料行存放區，此陳述式會重建叢集資料行存放區索引。

叢集數據行存放區的 ORDER

column_store_order_ordinal使用 sys.index_columns 中的數據行來判斷叢集數據行存放區索引的數據行順序。 數據行存放區排序有助於 區段消除，特別是字串數據。 如需詳細資訊，請參閱使用已排序叢集數據行存放區索引和數據行存放區索引的效能微調 - 設計指引。

若要轉換成已排序的叢集數據行存放區索引，現有的索引必須是叢集數據行存放區索引。 使用 DROP_EXISTING 選項。

LOB 資料類型 ((最長) 長度資料類型) 不能是已排序叢集資料行存放區索引的索引鍵。

建立已排序的叢集數據行存放區索引時，請使用 OPTION(MAXDOP = 1) 以取得最高品質的排序，以換取語句持續時間 CREATE INDEX 相當長的時間。 若要儘快建立索引，請勿限制 MAXDOP。 最高品質壓縮和排序有助於查詢資料行存放區索引。

如需已排序的數據行存放區索引可用性，請參閱 數據行存放區索引：概觀。

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 ]

針對磁碟型資料表，「delay」會指定處於關閉狀態的差異資料列群組必須在差異資料列群組中至少保留多少分鐘的時間。 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 （Transact-SQL） 。

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

指定包含索引之資料表的一部分、兩部分或三部分名稱。

非叢集數據行存放區的 ORDER

非叢集數據行存放區索引子句中指定的 ORDER 數據行必須是索引之索引之索引鍵數據行的子集。

column_store_order_ordinal使用 sys.index_columns 中的數據行來判斷非叢集數據行存放區索引的數據行順序。 數據行存放區排序有助於 區段消除，特別是字串數據。 如需詳細資訊，請參閱使用已排序叢集數據行存放區索引和數據行存放區索引的效能微調 - 設計指引。 這些文章中的設計和效能考慮通常同時適用於叢集和非叢集數據行存放區索引。

LOB 資料類型 （max） 長度數據類型不能是已排序的非叢集數據行存放區索引的索引鍵。

建立已排序的非叢集數據行存放區索引時，請使用 OPTION(MAXDOP = 1) 以取得最高品質的排序，以換取語句持續時間 CREATE INDEX 相當長的時間。 若要儘快建立索引，請勿限制 MAXDOP。 最高品質壓縮和排序有助於查詢資料行存放區索引。

如需已排序的數據行存放區索引可用性，請參閱 已排序的數據行索引可用性。

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 分鐘，該資料列就可壓縮成單欄式儲存體格式。

對於磁碟型資料表上的資料行存放區索引，不會追蹤插入或更新資料列的時間。 相反地，差異資料列群組關閉時間會當作資料列的 Proxy。 預設持續時間是 0 分鐘。 在差異數據列群組中累積 1 百萬個數據列之後，數據列會移轉至單欄式記憶體，並標示為已關閉。

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。

權限

需要資料表的 ALTER 權限。

備註

您可以在暫存資料表上建立資料行存放區索引。 當資料表卸除或工作階段結束時，也會卸除索引。

在 Fabric SQL 資料庫中，具有叢集數據行存放區索引的數據表不會 鏡像到 Fabric OneLake。

篩選的索引

「已篩選的索引」是最佳化的非叢集索引，適用於從資料表選取少數資料列的查詢使用。 它會使用篩選述詞，針對資料表中的部分資料建立索引。 設計良好的已篩選索引可以提升查詢效能、降低儲存成本，並減少維護成本。

需要已篩選之索引的 SET 選項

每當發生下列任何一個狀況時，都需要必要值資料行中的 SET 選項：

• 您可以建立篩選的索引。
• INSERT、UPDATE、DELETE 或 MERGE 作業修改已篩選之索引中的資料。
• 查詢最佳化工具會利用已篩選索引來產生查詢計劃。

Set 選項	必要值	預設伺服器值	預設 OLE DB 與 ODBC 值	預設 DB-Library 值
ANSI_NULLS	開啟	開啟	開啟	OFF
ANSI_PADDING	開啟	開啟	開啟	OFF
ANSI_WARNINGS 1	開啟	開啟	開啟	OFF
ARITHABORT	開啟	開啟	OFF	OFF
CONCAT_NULL_YIELDS_NULL	開啟	開啟	開啟	OFF
NUMERIC_ROUNDABORT	OFF	OFF	OFF	OFF
QUOTED_IDENTIFIER	開啟	開啟	開啟	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 和以上) 和所有虛擬核心供應項目層的 Azure SQL Database。

² 適用於 SQL Server 2014 (12.x) 和更新版本。

如果基礎資料表有一個資料行為資料行存放區索引不支援的資料類型，您必須在非叢集資料行存放區索引中省略該資料行。

大型物件 (LOB) 大於 8 KB 的資料會儲存在資料列外的 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 和更新版本），以及所有虛擬核心供應專案層級。 舊版不支援叢集和非叢集數據行存放區索引中的LOB類型。
• 從 SQL Server 2016 (13.x) 開始，您可以在索引檢視表上建立非叢集資料行存放區索引。

資料行存放區索引無法與下列功能結合：

• 計算資料行。 從 SQL Server 2017 （14.x）開始，叢集數據行存放區索引可以包含非保存的計算數據行。 不過，在 SQL Server 2017 （14.x）， 叢集數據行存放區索引不能包含保存的計算數據行，而且您無法在計算數據行上建立非叢集索引。
• 頁面和數據列壓縮，以及 vardecimal 記憶體格式。 (資料行存放區索引已使用不同格式壓縮。)
• 使用叢集數據行存放區索引進行複寫。 支援非叢集數據行存放區索引。 如需詳細資訊，請參閱 sp_addarticle。
• Filestream。

您無法在具有叢集資料行存放區索引的資料表上，使用資料指標或觸發程序。 此限制不適用於非叢集資料行存放區索引。 您可以在具有非叢集資料行存放區索引的資料表上，使用資料指標和觸發程序。

SQL Server 2014 (12.x) 具體限制：

下列限制僅適用於 SQL Server 2014 (12.x)。 在此版本中，您可以使用可更新的叢集資料行存放區索引。 非叢集資料行存放區索引仍為唯讀。

• 變更追蹤。 您無法搭配資料行存放區索引使用變更追蹤。
• 異動資料擷取。 此功能無法在具有叢集資料行存放區索引的資料表上啟用。 從 SQL Server 2016 （13.x）開始，變更數據擷取可以在具有非叢集數據行存放區索引的數據表上啟用。
• 可讀取的次要複本。 您無法從 Always On 可讀取可用性群組的可讀取次要複本，存取叢集資料行存放區索引 (CCI)。 您可以從可讀取的次要複本來存取非叢集資料行存放區索引 (NCCI)。
• Multiple Active Result Sets (MARS)。 SQL Server 2014 (12.x) 使用此功能，與具有資料行存放區索引的資料表進行唯讀連線。 不過，SQL Server 2014 (12.x) 不支援此功能在具備資料行存放區索引的資料表上，進行並行資料操作語言 (DML) 作業。 如果您嘗試將此功能用於此用途，SQL Server 會終止連線並取消交易。
• 無法在檢視表或索引檢視表上建立非叢集資料行存放區索引。

如需數據行存放區索引效能優點和限制的相關信息，請參閱 數據行存放區索引：概觀。

中繼資料

資料行存放區索引中的所有資料行都將儲存於中繼資料內成為內含資料行。 資料行存放區索引沒有索引鍵資料行。 下列系統檢視提供資料行存放區索引的相關資訊：

• 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. 將堆積轉換成叢集資料行存放區索引

此範例會建立資料表當作堆積，然後將它轉換成名為 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. 將資料列存放區資料表轉換成資料行存放區索引時，處理非叢集索引

這個範例說明將資料列存放區資料表轉換成資料行存放區索引時，如何處理非叢集索引。 從 2016 SQL Server (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. 將大型事實資料表從資料列存放區轉換成資料行存放區

此範例說明如何將大型事實資料表從資料列存放區資料表轉換成資料行存放區資料表。

1. 建立一個小型資料表以供此範例使用。

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

2. 卸除資料列存放區資料表中所有的非叢集索引。 建議您編寫索引以便稍後重新建立。

   --Drop all nonclustered indexes
   DROP INDEX my_index ON dbo.MyFactTable;
   

3. 將資料列存放區資料表轉換成具有叢集資料行存放區索引的資料行存放區資料表。

   首先，查詢現有叢集資料列存放區索引的名稱。 在步驟 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. 將資料行存放區資料表轉換成具有叢集索引的資料列存放區資料表。

若要將資料行存放區資料表轉換成具有叢集索引的資料列存放區資料表，請使用 CREATE INDEX 陳述式搭配 DROP_EXISTING 選項。

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. 將叢集索引轉換成叢集資料行存放區索引

您可以使用 CREATE CLUSTERED COLUMNSTORE INDEX 陳述式和 DROP_EXISTING = ON 來完成以下事項：

• 將叢集索引轉換成叢集資料行存放區索引。

• 重建叢集資料行存放區索引。

此範例會將 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';

現在您可以選擇：

1. 卸除使用自動建立名稱的現有叢集資料行存放區索引，然後以使用者定義的名稱建立新的叢集資料行存放區索引。
2. 卸除現有索引，並以叢集資料行存放區索引取代，保留相同的系統產生名稱，例如 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 卸除叢集資料行存放區索引，並將資料表轉換成數據列存放區堆積。 這個範例會將 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. 在沒有索引的資料表上建立已排序的叢集資料行存放區索引

未排序的資料行存放區索引預設涵蓋所有資料行，不需要指定資料行清單。 已排序的資料行存放區索引可讓您指定資料行的順序。 清單不需要包含所有資料行。

如需詳細資訊，請參閱 使用已排序的叢集數據行存放區索引進行效能微調。

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. 將資料行新增到已排序叢集資料行存放區索引的順序

您可以指定資料列存放區索引中資料行的順序。 原始已排序的叢集資料行存放區索引只會在 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. 建立已排序的叢集資料行存放區索引

您可以使用排序索引鍵，建立叢集資料行存放區索引。 建立已排序的叢集數據行存放區索引時，您應該套用查詢提示 MAXDOP = 1 ，以取得排序品質上限和最短持續時間。 如需已排序的數據行存放區索引可用性，請參閱 數據行存放區索引：概觀。

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

相關內容

• 資料行存放區索引：概觀
• 資料行存放區索引的新功能
• 開始使用資料行存放區進行即時作業分析
• 數據倉儲中的數據行存放區索引
• 資料行存放區索引 - 查詢效能
• 資料行存放區索引 - 設計指導