CREATE COLUMNSTORE INDEX (Transact-SQL)

在 SQL Server 資料表上建立非叢集記憶體中資料行存放區索引。 使用非叢集資料行存放區索引，可利用資料行存放區壓縮，大幅改善唯讀資料的查詢執行時間。

若要建立叢集資料行存放區索引，請參閱＜CREATE CLUSTERED COLUMNSTORE INDEX (Transact-SQL)＞。

如需詳細資訊，請參閱下列主題：

• 資料行存放區索引說明

• 使用非叢集資料行存放區索引

• 使用叢集資料行存放區索引

Transact-SQL 語法慣例

語法

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

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

引數

• index_name
  指定索引的名稱。 index_name 在資料表中必須是唯一的，但在資料庫中不需要是唯一的。 索引名稱必須遵照識別碼的規則。

  對於非叢集資料行存放區索引，

• ( column [ ,...n] )
  指定要存放的資料行。 非叢集資料行存放區索引僅限於 1024 個資料行。

  每個資料行必須是資料行存放區索引支援的資料類型。 如需支援的資料類型清單，請參閱＜限制事項＞。

• ON [database_name.[schema_name ] .| schema_name .] table_name
  指定將包含索引之資料表的一部分、兩部分或三部分名稱。

• ON
  這些選項指定將建立索引的檔案群組。

  • partition_scheme_name ( column_name )
    指定分割區配置來定義要做為資料分割索引之分割區對應目標的檔案群組。 分割區配置必須存在於資料庫中，方法是執行 CREATE PARTITION SCHEME。 column_name 指定資料分割索引將進行分割的資料行。 此資料行必須符合 partition_scheme_name 所使用之資料分割函數引數的資料類型、長度與有效位數。 column_name 不限定為索引定義中的資料行。 對資料行存放區索引進行分割時，如果未指定分割區資料行，Database Engine 會將它新增為索引的資料行。

    如果未指定 partition_scheme_name 或 filegroup，且已分割資料表，則會利用相同的分割區資料行，將索引放在與基礎資料表相同的分割區配置中。

    分割資料表的資料行存放區索引必須保持分割區對齊。

    如需有關資料分割索引的詳細資訊，請參閱＜分割資料表與索引＞。

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

  • "default"
    在預設的檔案群組上建立指定的索引。

    在這個內容中，default 這個字不是關鍵字。 它是預設檔案群組的識別碼，必須加以分隔，例如 ON "default" 或 ON [default]。 如果指定了 "default"，目前工作階段的 QUOTED_IDENTIFIER 選項就必須是 ON。 這是預設設定。 如需詳細資訊，請參閱＜SET QUOTED_IDENTIFIER (Transact-SQL)＞。

  • DROP_EXISTING
    指定要卸除及重建預先存在的具名索引。 預設值為 OFF。

    • ON
      卸除及重建現有的索引。 所指定的索引名稱必須與目前現有的索引相同；不過，索引定義可以修改。 例如，您可以指定不同的資料行或索引選項。
    • OFF
      如果所指定的索引名稱已存在，畫面上會出現錯誤。 您無法利用 DROP_EXISTING 來變更索引類型。 在與舊版本相容的語法中，WITH DROP_EXISTING 相當於 WITH DROP_EXISTING = ON。

  • MAXDOP = max_degree_of_parallelism
    針對索引作業持續時間覆寫 設定 max degree of parallelism 伺服器組態選項 組態選項。 請利用 MAXDOP 來限制執行平行計畫所用的處理器數目。 最大值是 64 個處理器。

    max_degree_of_parallelism 值可能是：

    • 1 - 隱藏平行計畫的產生。

    • >1 - 根據目前的系統工作負載，將平行索引作業所使用的處理器數目上限，限制為所指定的數目或更少的數目。 例如，當 MAXDOP = 4，使用的處理器數目將會等於或小於 4。

    • 0 (預設值) - 根據目前的系統工作負載來使用實際數目的處理器或比實際數目更少的處理器。

    如需詳細資訊，請參閱＜設定平行索引作業＞。

    注意

    並非 Microsoft SQL Server 的每個版本都無法使用平行索引作業。如需 SQL Server 版本所支援功能的清單，請參閱＜SQL Server 2014 版本支援的功能＞。

權限

需要資料表的 ALTER 權限。

一般備註

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

如果資料行不屬於資料行存放區索引支援的資料類型，該資料行必須從資料行存放區索引剔除。

限制事項

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

• 不能有 1024 個以上的資料行。

• 具有非叢集資料行存放區索引的資料表可以有唯一條件約束、主索引鍵條件約束或外部索引鍵條件約束，但是條件約束不得包含在非叢集資料行存放區索引內。

• 無法在檢視表或索引檢視表上建立。

• 不能包含疏鬆資料行。

• 無法使用 ALTER INDEX 陳述式加以變更。 若要變更非叢集索引，您必須先卸除再重新建立資料行存放區索引。 您可以使用 ALTER INDEX 停用並重建資料行存放區索引。

• 無法使用 INCLUDE 關鍵字來建立。

• 不可包含 ASC 或 DESC 關鍵字排序索引。 資料行存放區索引是依據壓縮演算法來排序。 遞增或遞減排序會取消許多效能優點。

資料行存放區索引中的每個資料行必須是下列其中一種一般商務資料類型。

• datetimeoffset [ ( n ) ]

• datetime2 [ ( n ) ]

• datetime

• smalldatetime

• date

• time [ ( n ) ]

• float [ ( n ) ]

• real [ ( n ) ]

• decimal [ ( precision [ , scale ] ) ]

• money

• smallmoney

• bigint

• int

• smallint

• tinyint

• bit

• nvarchar [ ( n ) ] 除了 nvarchar(max) 不受支援

• nchar [ ( n ) ]

• varchar [ ( n ) ]

• char [ ( n ) ]

• varbinary [ ( n ) ] 除了 varbinary (max) 不受支援

• binary [ ( n ) ]

• uniqueidentifier

使用任何下列資料類型的資料行不可加入資料行存放區索引。

• ntext、text 和 image

• varchar(max) 和 nvarchar(max)

• rowversion (和 timestamp)

• sql_variant

• CLR 類型 (hierarchyid 和空間類型)

• xml

• uniqueidentifier

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

• 頁面和資料列壓縮，以及 vardecimal 儲存格式 (資料行存放區索引已使用不同格式壓縮)。

• 複寫

• 變更追蹤

• 異動資料擷取

• 檔案資料流

如需有關資料行存放區索引之效能優點和限制的詳細資訊，請參閱＜資料行存放區索引說明＞。

中繼資料

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

• sys.indexes (Transact-SQL)

• sys.index_columns (Transact-SQL)

• sys.partitions (Transact-SQL)

• sys.column_store_segments (Transact-SQL)

• sys.column_store_dictionaries (Transact-SQL)

• sys.column_store_row_groups (Transact-SQL)

[回到頁首]

範例

A.建立簡單的非叢集資料行存放區索引

下列範例會建立簡單資料表和叢集索引，然後示範建立非叢集資料行存放區索引的語法。

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

B.使用所有選項建立簡單的非叢集索引

下列範例示範利用所有選項建立非叢集資料行存放區索引的語法。

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

如需使用資料分割資料表的較複雜範例，請參閱＜資料行存放區索引說明＞。

變更非叢集資料行存放區索引中的資料

一旦您在資料表上建立非叢集資料行存放區索引，就無法直接修改該資料表中的資料。 使用 INSERT、UPDATE、DELETE 或 MERGE 的查詢將會失敗，並傳回錯誤訊息。 若要加入或修改資料表中的資料，您可以執行下列其中一項操作：

• 停用或卸除資料行存放區索引。 然後您就可以更新資料表中的資料。 如果您停用資料行存放區索引，您可以在完成更新資料時重建資料行存放區索引。 例如，

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

• 將資料載入未包含資料行存放區索引的暫存資料表。 在暫存資料表上建立資料行存放區索引。 將暫存資料表切換至主資料表的空白分割區。

• 從具有資料行存放區索引的資料表分割區切換至空白的暫存資料表。 如果暫存資料表上有資料行存放區索引，請停用資料行存放區索引。 執行所有更新。 建立 (或重建) 資料行存放區索引。 將暫存資料表切換回 (現在為空白的) 主資料表分割區。

[回到頁首]