CREATE COLUMNSTORE INDEX (Transact-SQL)

發行項
01/25/2023

適用于：SQL Server Azure SQL資料庫 Azure SQL 受控執行個體 Azure Synapse Analytics Analytics Platform System (PDW)

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

已排序的叢集資料行存放區索引是在 2022 SQL Server 2022 (16.x) 中引進。如需詳細資訊，請參閱 CREATE COLUMNSTORE INDEX。
從 SQL Server 2016 (13.x) 開始，您可以將資料表建立為叢集資料行存放區索引。您不再需要先建立資料列存放區資料表，然後將它轉換成叢集資料行存放區索引。
如需資料行存放區索引設計指導方針的詳細資訊，請參閱資料行存放區索引 - 設計指引。

語法

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 }
    [ WITH ( <with_option> [ , ...n ] ) ]
    [ ON <on_option> ] | [ ORDER <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分析、平行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 與更早版本的 Transact-SQL 語法，請參閱舊版文件。

引數

某些選項不適用於所有資料庫引擎版本。下表說明當在 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

適用于 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，並使用伺服器可以提供的所有平行線程。壓縮和排序的最高品質有助於資料行存放區索引的查詢。

建立已排序的叢集資料行存放區索引時，索引鍵資料行會以 column_store_order_ordinal 中的資料 sys.index_columns 行表示。

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 在資料表內必須是唯一的，但不需要在資料庫中是唯一的。索引名稱必須遵照識別碼的規則。

( 資料行 [ ,...n ] )

指定要存放的資料行。非叢集資料行存放區索引僅限 1024 個資料行。每個資料行必須是資料行存放區索引支援的資料類型。如需支援的資料類型清單，請參閱限制和限制。

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 2016 的版本和支援的功能。

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 不限於索引定義中的資料行。分割資料行存放區索引時，如果尚未指定資料行，Database Engine 會將資料分割資料行新增為索引的資料行。

如果資料表已分割，而且未指定 partition_scheme_name 或 檔案群組 ，則索引會放在相同的資料分割配置中，並使用與基礎資料表相同的資料分割資料行。

分割資料表的資料行存放區索引必須保持分割區對齊。如需資料分割索引的詳細資訊，請參閱資料分割資料表和索引。

filegroup_name

指定索引建立所在的檔案群組名稱。如果未指定 filegroup_name 且未分割資料表，則索引會使用與基礎資料表相同的檔案群組。此檔案群組必須已存在。

"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	開啟	開啟	開啟	OFF
ANSI_PADDING	開啟	開啟	開啟	OFF
ANSI_WARNINGS ¹	開啟	開啟	開啟	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 選項不正確時，可能會發生下列狀況：

未建立篩選的索引。
Database Engine 會產生錯誤，並回復變更索引中資料的 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²

¹適用于 2017 SQL Server 2017 (14.x) ，並在進階層、標準層 (S3 和) 更新版本中Azure SQL資料庫，以及叢集資料行存放區索引中的所有虛擬核心供應專案層。

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

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

大型物件 (LOB) 大於 8 KB 的資料會儲存在 off-row、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 (和 時間戳記)
sql_variant
CLR 類型 (hierarchyid 和空間類型)
xml
uniqueidentifier²

¹適用于 SQL Server 2016 (13.x) 和舊版，以及非叢集資料行存放區索引。

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

非叢集資料行存放區索引：

不能超過 1024 個數據行。
無法建立為條件約束式索引。在具有資料行存放區索引的資料表上，可以有唯一的條件約束、主鍵條件約束和外鍵條件約束。條件約束一律會使用資料列存放區索引強制實施。無法使用資料行存放區 (叢集或非叢集) 索引強制執行條件約束。
不能包含疏鬆資料行。
無法使用 ALTER INDEX 語句來變更。若要變更非叢集索引，您必須先卸除再重新建立資料行存放區索引。您可以使用 ALTER INDEX 來停用並重建資料行存放區索引。
無法使用 INCLUDE 關鍵字來建立。
無法包含用於排序索引的 ASC 或 DESC 關鍵字。資料行存放區索引是依據壓縮演算法來排序。遞增或遞減排序會取消許多效能優點。在 Azure Synapse Analytics 中，從 SQL Server 2022 (16.x) 開始，您可以指定資料行存放區索引中的資料行順序。    如需詳細資訊，請參閱使用已排序的叢集資料行存放區索引進行效能微調。
無法在非叢集資料行存放區索引中包含 Nvarchar 類型的 LOB 資料行 (max) 、Varchar (max) ，以及 Varbinary (非叢集資料行存放區索引中的 max) 。只有叢集資料行存放區索引支援 LOB 類型，從 SQL Server 2017 (14.x) 版本開始，Azure SQL在進階層、標準層 [S3 和更新版本] 設定的資料庫 (，以及所有虛擬核心供應專案層) 。請注意，舊版不支援叢集和非叢集資料行存放區索引中的 LOB 類型。
從 SQL Server 2016 (13.x) 開始，您可以在索引檢視表上建立非叢集資料行存放區索引。

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

計算資料行。從 SQL Server 2017 開始，叢集資料行存放區索引可以包含非保存的計算資料行。不過，在 SQL Server 2017 中，叢集資料行存放區索引不能包含保存的計算資料行，而且您無法在計算資料行上建立非叢集索引。
頁面和資料列壓縮，以及 vardecimal 儲存格式。 (資料行存放區索引已以不同的格式壓縮。)
複寫。
Filestream。

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

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

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

變更追蹤。您無法搭配資料行存放區索引使用變更追蹤。
異動資料擷取。此功能無法在具有叢集資料行存放區索引的資料表上啟用。從 SQL Server 2016 開始，可以在具有非叢集資料行存放區索引的資料表上啟用異動資料擷取。
可讀取的次要複本。您無法從Always On可讀取可用性群組的可讀取次要複本存取叢集資料行存放區索引 (CCI) 。您可以從可讀取的次要複本來存取非叢集資料行存放區索引 (NCCI)。
Multiple Active Result Sets (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. 將資料列存放區資料表轉換成資料行存放區索引時，處理非叢集索引

此範例示範如何將資料列存放區資料表轉換成資料行存放區索引時處理非叢集索引。從 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. 將大型事實資料表從資料列存放區轉換成資料行存放區

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

建立要在此範例中使用的小型資料表。

--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. 將資料行存放區資料表轉換成具有叢集索引的資料列存放區資料表。

若要將資料行存放區資料表轉換成具有叢集索引的資料列存放區資料表，請使用 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 檔案群組上建立非叢集資料行存放區索引的語法，並將平行處理原則的最大程度指定為 2 (MAXDOP) 。

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

C. 使用篩選述詞來建立非叢集資料行存放區索引

下列範例會在範例資料庫中的 Production.BillOfMaterials 資料表 AdventureWorks2019 上建立篩選的非叢集資料行存放區索引。篩選述詞可以包含不是篩選索引中索引鍵資料行的資料行。此範例中的述詞只會選取非 Null 的資料 EndDate 列。

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 2016 (13.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';

現在，您可以選擇：

卸載具有自動建立名稱的現有叢集資料行存放區索引，然後使用使用者定義的名稱建立新的叢集資料行存放區索引。
卸載現有的索引，並以叢集資料行存放區索引取代現有索引，並保留相同的系統產生名稱，例如 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. 變更已排序資料行的序數

原始排序的叢集資料行存放區索引在上 SHIPDATEPRODUCTKEY 排序。下列範例會將排序變更為 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

( 資料行 [ ,...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. 建立已排序的叢集資料行存放區索引

另請參閱

下一步

其他資源

其他資源