CREATE COLUMNSTORE INDEX (Transact-SQL)
適用対象:
SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)
行ストア テーブルをクラスター化列ストア インデックスに変換するか、非クラスター化列ストア インデックスを作成します。 OLTP ワークロードに対して効率的にリアルタイム運用分析を実行するには、またはデータ ウェアハウスに対するワークロードのデータ圧縮とクエリのパフォーマンスを向上させるには、列ストア インデックスを使用します。
この機能の最新の機能強化については、「列ストア インデックスの新機能」を参照してください。
順序指定クラスター化列ストア インデックスは、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 }
[ ORDER (column [ , ...n ] ) ]
[ WITH ( <with_option> [ , ...n ] ) ]
[ ON <on_option> ]
[ ; ]
-- Create a nonclustered columnstore index on a disk-based table.
CREATE [ NONCLUSTERED ] COLUMNSTORE INDEX index_name
ON { database_name.schema_name.table_name | schema_name.table_name | table_name }
( column [ , ...n ] )
[ WHERE <filter_expression> [ AND <filter_expression> ] ]
[ WITH ( <with_option> [ , ...n ] ) ]
[ ON <on_option> ]
[ ; ]
<with_option> ::=
DROP_EXISTING = { ON | OFF } -- default is OFF
| MAXDOP = max_degree_of_parallelism
| ONLINE = { ON | OFF }
| COMPRESSION_DELAY = { 0 | delay [ MINUTES ] }
| DATA_COMPRESSION = { COLUMNSTORE | COLUMNSTORE_ARCHIVE }
[ ON PARTITIONS ( { partition_number_expression | range } [ , ...n ] ) ]
<on_option>::=
partition_scheme_name ( column_name )
| filegroup_name
| "default"
<filter_expression> ::=
column_name IN ( constant [ , ...n ]
| column_name { IS | IS NOT | = | <> | != | > | >= | !> | < | <= | !< } constant )
Azure Synapse Analytics、Parallel Data Warehouse、SQL Server 2022 (16.x) 以降のバージョンの構文:
CREATE CLUSTERED COLUMNSTORE INDEX index_name
ON { database_name.schema_name.table_name | schema_name.table_name | table_name }
[ ORDER ( column [ , ...n ] ) ]
[ WITH ( DROP_EXISTING = { ON | OFF } ) ] -- default is OFF
[;]
Note
SQL Server 2014 (12.x) 以前のバージョンの 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 句 | 該当なし | 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
クラスター化列ストア インデックスとして格納するテーブルの 1 部、2 部、または 3 部構成の名前を指定します。 テーブルがヒープであるかクラスター化インデックスを含む場合、テーブルは行ストアから列ストアに変換されます。 テーブルが既に列ストアである場合、このステートメントでクラスター化列ストア インデックスが再構築されます。
ORDER
Azure Synapse Analytics、Analytics Platform System (PDW)、SQL Server 2022 (16.x) 以降に適用されます
sys.index_columnsのcolumn_store_order_ordinal列を使用して、クラスター化列ストア インデックスの列の順序を決定します。 これは、セグメントの削除、特に文字列データに役立ちます。 詳細については、「順序付けされたクラスター化列ストア インデックスと列ストア インデックスを使用したパフォーマンスチューニング - 設計ガイダンス」を参照してください。
順序付けされたクラスター化列ストア インデックスに変換するには、既存のインデックスがクラスター化列ストア インデックスである必要があります。 DROP_EXISTING オプションを使用します。
LOB データ型 ((最大) 長データ型) を、順序指定クラスター化列ストア インデックスのキーにすることはできません。
順序指定クラスター化列ストア インデックスを作成する場合、CREATE INDEX ステートメントの期間が大幅に長くなるのと引き換えに、CREATE INDEX ステートメントでの最高品質の並べ替えを実現するために OPTION(MAXDOP = 1) を使用します。 インデックスをできるだけ高速に作成するには、MAXDOP を制限せず、サーバーで提供できるすべての並列スレッドを使用します。 最高品質の圧縮と並べ替えは、列ストア インデックスに対するクエリに役立つ場合があります。
順序指定クラスター化列ストア インデックスが作成されると、キー列は sys.index_columns の column_store_order_ordinal 列によって示されます。
WITH オプション
DROP_EXISTING = [OFF] | ON
DROP_EXISTING = ON の場合、既存のインデックスを削除し、新しい列ストア インデックスを作成します。
CREATE CLUSTERED COLUMNSTORE INDEX cci ON Sales.OrderLines
WITH (DROP_EXISTING = ON);
既定の DROP_EXISTING = OFF では、既存の名前と同じインデックス名が期待されます。 指定したインデックス名が既に存在する場合は、エラーが発生します。
MAXDOP = max_degree_of_parallelism
このオプションでは、インデックス操作中に、既存のサーバーの最大並列度構成をオーバーライドできます。 並列プランの実行で使用されるプロセッサ数を制限するには、MAXDOP を使用します。 最大数は 64 プロセッサです。
max_degree_of_parallelism 値に指定できる値:
- 1。並列プランの生成が中止します。
- >1。現在のシステム ワークロードに基づいて、並列インデックス操作で使用される最大プロセッサ数を指定の数以下に制限します。 たとえば、MAXDOP が 4 の場合、使用されるプロセッサの数は 4 以下になります。
- 0 (既定)。現在のシステム ワークロードに基づいて、実際の数以下のプロセッサを使用します。
CREATE CLUSTERED COLUMNSTORE INDEX cci ON Sales.OrderLines
WITH (MAXDOP = 2);
詳細については、「 並列処理の最大限度の構成 (サーバー構成オプション)」および 「並列インデックス操作の構成」を参照してください。
COMPRESSION_DELAY = 0 | delay [ MINUTES ]
ディスク ベースのテーブルの場合は、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」をご覧ください。
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
インデックスが含まれているテーブルの 1 部、2 部、または 3 部構成の名前を指定します。
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 (既定)。現在のシステム ワークロードに基づいて、実際の数以下のプロセッサを使用します。
詳細については、「 並列インデックス操作の構成」を参照してください。
Note
並列インデックス操作は、Microsoft SQL Server のすべてのエディションで使用できるわけではありません。 SQL Server の各エディションでサポートされる機能の一覧については、「SQL Server 2022 の各エディションとサポートされている機能」を参照してください。
ONLINE = [ON | OFF]
ONの場合、インデックスの新しいコピーが構築されている間、列ストア インデックスはオンラインのままで、利用可能です。OFFの場合、新しいコピーが構築されている間、インデックスは使用できません。 非クラスター化インデックスでは、ベース テーブルを引き続き使用できます。 新しいインデックスが完成するまでのクエリを満たすために、非クラスター化列ストア インデックスのみが使用されるわけではありません。
CREATE COLUMNSTORE INDEX ncci ON Sales.OrderLines (StockItemID, Quantity, UnitPrice, TaxRate)
WITH ( ONLINE = ON );
COMPRESSION_DELAY = 0 | delay [ MINUTES ]
行がデルタ行グループに残る期間の下限を指定します。この下限までは、圧縮された行グループに移行できます。 たとえば、行が 120 分間変更されない場合、行をカラム型ストレージ形式に圧縮することができます。
ディスク ベースのテーブルの列ストア インデックスの場合、行が挿入または更新された時刻は追跡されません。 代わりに、デルタ行グループの終了時刻が行のプロキシとして使用されます。 既定の継続時間は 0 分です。 行は、デルタ行グループに 100 万行が蓄積され、閉じられたとマークされた後に、列ストレージに移行されます。
DATA_COMPRESSION
指定したテーブル、パーティション番号、またはパーティション範囲に、データ圧縮オプションを指定します。 非クラスター化とクラスター化の両方を含む列ストア インデックスにのみ適用されます。 次のようなオプションがあります。
COLUMNSTOREは既定で、最もパフォーマンスの高い列ストア圧縮を使用して圧縮します。 このオプションは一般的な選択肢です。COLUMNSTORE_ARCHIVEでは、さらにテーブルまたはパーティションを小さいサイズに圧縮します。 このオプションは、アーカイブ用や、ストレージのサイズを減らす必要があり、かつ保存と取得に時間をかける余裕があるその他の状況で使用できます。
圧縮の詳細については、「データ圧縮」を参照してください。
WHERE <filter_expression> [ AND <filter_expression> ]
フィルター述語が呼び出されると、このオプションがインデックスに含める行を指定します。 SQL Server は、フィルター選択されたインデックスのデータ行で、フィルター選択された統計情報を作成します。
フィルター述語では、単純な比較ロジックが使用されます。 リテラルを使用 NULL する比較は、比較演算子では使用できません。 代わりに、IS NULL および IS NOT NULL 演算子を使用します。
次に、Production.BillOfMaterials テーブルのフィルター述語の例をいくつか示します。
WHERE StartDate > '20000101' AND EndDate <= '20000630'WHERE ComponentID IN (533, 324, 753)WHERE StartDate IN ('20000404', '20000905') AND EndDate IS NOT NULL
フィルター選択されたインデックスについては、「フィルター選択されたインデックスの作成」を参照してください。
ON オプション
次のオプションによって、インデックスが作成されるファイル グループが指定されます。
partition_scheme_name ( column_name )
パーティション インデックスのパーティションのマップ先となる、ファイル グループを定義するパーティション構成を指定します。 CREATE PARTITION SCHEME を実行し、パーティション構成がデータベース内に存在するようにする必要があります。
column_name には、パーティション インデックスがパーティション分割される対象の列を指定します。 この列は、partition_scheme_name で使用されているパーティション関数の引数のデータ型、長さ、有効桁数に一致する必要があります。 column_name は、インデックス定義で指定されている列に限定されません。 列ストア インデックスをパーティション分割するとき、データベース エンジンでは、まだ指定されていないパーティション分割列がインデックスの列として追加されます。
テーブルがパーティション分割されていて、partition_scheme_name または filegroup が指定されていない場合は、インデックスは同じパーティション構成に配置され、基になるテーブルと同じパーティション分割列を使用します。
パーティション テーブルの列ストア インデックスは、パーティション固定にする必要があります。 インデックスのパーティション分割の詳細については、「パーティション テーブルとパーティション インデックス」を参照してください。
filegroup_name
インデックスを作成するファイル グループ名を指定します。 filegroup_name の指定がなく、テーブルがパーティション分割されていない場合は、基になるテーブルと同じファイル グループが使用されます。 ファイル グループは既に存在している必要があります。
"default"
既定のファイル グループに、指定したインデックスを作成します。
このコンテキストでの default という用語はキーワードではありません。 これは、既定ファイル グループの識別子なので、ON "default" または ON [default] のように区切る必要があります。 "default" を指定する場合は、現在のセッションに対して QUOTED_IDENTIFIER オプションが ON である必要があります。これは既定の設定です。 詳細については、「SET QUOTED_IDENTIFIER (Transact-SQL)」をご覧ください。
アクセス許可
テーブルに対する ALTER 権限が必要です。
解説
列ストア インデックスは、一時テーブルに作成することができます。 テーブルが削除されるかセッションが終了すると、インデックスも削除されます。
フィルター選択されたインデックス
"フィルター選択されたインデックス" は、最適化された非クラスター化インデックスであり、テーブルから選択する行の少ないクエリに適しています。 フィルター選択されたインデックスは、フィルター述語を使用してテーブル内の一部のデータにインデックスを作成します。 フィルター選択されたインデックスを適切に設計すると、クエリのパフォーマンスを向上させ、ストレージ コストとメンテナンス コストを削減することができます。
フィルター選択されたインデックスに必要な SET オプション
次の条件のいずれかに該当する場合、"必要な値" 列の SET オプションが必要となります。
- フィルター選択されたインデックスを作成する。
- INSERT、UPDATE、DELETE、MERGE のいずれかの操作で、フィルター選択されたインデックスのデータを変更する。
- クエリ オプティマイザーで、クエリ プランの生成にフィルター選択されたインデックスが使用される。
| SET オプション | 必須値 | 既定のサーバー値 | 既定の OLE DB および ODBC 値 | 既定の DB-Library 値 |
|---|---|---|---|---|
| ANSI_NULLS | ON | ON | ON | OFF |
| ANSI_PADDING | ON | ON | ON | OFF |
| ANSI_WARNINGS 1 | ON | ON | ON | OFF |
| ARITHABORT | ON | ON | OFF | OFF |
| CONCAT_NULL_YIELDS_NULL | ON | ON | ON | OFF |
| NUMERIC_ROUNDABORT | OFF | OFF | OFF | OFF |
| QUOTED_IDENTIFIER | ON | ON | ON | OFF |
1 ANSI_WARNINGS を ON に設定すると、データベース互換レベルが 90 以上に設定されている場合、暗黙的に 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)1
- nchar [ ( n ) ]
- varchar [ ( n ) ]
- varchar(max)1
- char [ ( n ) ]
- varbinary [ ( n ) ]
- varbinary(max)1
- binary [ ( n ) ]
- uniqueidentifier2
1 クラスター化列ストア インデックスのみで、SQL Server 2017 (14.x) と、Premium レベル、Standard レベル (S3 以上)、すべての仮想コア サービス レベルの Azure SQL Database に適用されます。
2 SQL Server 2014 (12.x) 以降のバージョンに適用されます。
基になるテーブルに列ストア インデックスでサポートされていないデータ型の列がある場合は、非クラスター化列ストア インデックスからは、その列を省く必要があります。
8 キロバイトを超えるラージ オブジェクト (LOB) データは行外 LOB ストレージに格納され、物理的な場所を示すポインターだけが列セグメント内に格納されます。 格納データのサイズは、sys.column_store_segments、sys.column_store_dictionaries、または sys.dm_db_column_store_row_group_physical_stats では報告されません。
以下のいずれかのデータ型を使用する列は、列ストア インデックスに含めることができません。
- ntext、text、image
- nvarchar(max)、varchar(max)、varbinary(max)1
- rowversion (および timestamp)
- sql_variant
- CLR 型 (hierarchyid 型と空間型)
- xml
- uniqueidentifier2
1 SQL Server 2016 (13.x) 以前のバージョンと、非クラスター化列ストア インデックスに適用されます。
2 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 列を含めることはできません。 SQL Server 2017 (14.x) バージョン、Azure SQL Database (プレミアム レベル、Standard レベル (S3 以降)、およびすべての仮想コア オファリングレベルで構成) 以降、LOB の種類をサポートするのはクラスター化列ストア インデックスのみです。 以前のバージョンでは、クラスター化列ストア インデックスと非クラスター化列ストア インデックスの LOB 型はサポートされていません。
- SQL Server 2016 (13.x) 以降では、インデックス付きビューに対して非クラスター化列ストア インデックスを作成できます。
列ストア インデックスと同時に使用できない機能:
- 計算列。 SQL Server 2017 (14.x) 以降では、クラスター化列ストア インデックスに非永続化計算列を含めることができます。 ただし、SQL Server 2017 (14.x) では、クラスター化列ストア インデックスに保存された計算列を含めることはできません。また、計算列に非クラスター化インデックスを作成することはできません。
- ページと行の 圧縮、および vardecimal ストレージ形式。 (列ストア インデックスは、既に別の形式で圧縮されています。)
- レプリケーション。
- Filestream。
クラスター化列ストア インデックスを使用しているテーブルでは、カーソルやトリガーは使用できません。 この制限は非クラスター化列ストア インデックスには適用されません。 非クラスター化列ストア インデックスを使用しているテーブルでは、カーソルとトリガーを使用できます。
SQL Server 2014 (12.x) に固有の制限事項:
次の制限は SQL Server 2014 (12.x) にのみ適用されます。 このリリースでは、更新可能なクラスター化列ストア インデックスを使用できます。 非クラスター化列ストア インデックスは引き続き読み取り専用です。
- 変更の追跡。 列ストア インデックスで変更履歴を使用することはできません。
- 変更データ キャプチャ。 クラスター化列ストア インデックスを持つテーブルでは、この機能を有効にできません。 SQL Server 2016 (13.x) 以降では、非クラスター化列ストア インデックスを持つテーブルで変更データ キャプチャを有効にできます。
- 読み取り可能セカンダリ。 AlwaysOn の読み取り可能な可用性グループの読み取り可能なセカンダリからクラスター化列ストア インデックス (CCI) にアクセスすることはできません。 読み取り可能セカンダリから非クラスター化列ストア インデックス (NCCI) にアクセスできます。
- 複数のアクティブな結果セット (MARS)。 SQL Server 2014 (12.x) では、列ストア インデックスを含むテーブルに読み取り専用で接続するために、この機能が使用されます。 ただし、SQL Server 2014 (12.x) では、列ストア インデックスを含むテーブルでデータ操作言語 (DML) を同時操作する場合、この機能を利用できません。 この目的でこの機能を使用しようとすると、SQL Server は接続を終了し、トランザクションを取り消します。
- ビューまたはインデックス付きビューに対して非クラスター化列ストア インデックスを作成することはできません。
列ストア インデックスのパフォーマンス上の利点と制限については、「列ストア インデックス: 概要」を参照してください。
Metadata
列ストア インデックス内のすべての列は、付加列としてメタデータに格納されます。 列ストア インデックスはキー列を持ちません。 列ストア インデックスに関する情報は、次のシステム ビューによって提供されます。
- 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. 行ストア テーブルを列ストア インデックスに変換するときに、非クラスター化インデックスを処理する
この例では、行ストア テーブルを列ストア インデックスに変換するときに、非クラスター化インデックスを処理する方法を示します。 SQL Server 2016 (13.x) 以降は、特別なアクションは不要です。 SQL Server は新しいクラスター化列ストア インデックスで非クラスター化インデックスを自動的に定義し、再構築します。
非クラスター化インデックスを削除する場合は、列ストア インデックスを作成する前に DROP INDEX ステートメントを使用します。 DROP EXISTING オプションは、変換されるクラスター化インデックスのみを削除します。 非クラスター化インデックスは削除されません。
SQL Server 2012 (11.x) と SQL Server 2014 (12.x) では、列ストア インデックスに非クラスター化インデックスを作成できません。
--Create the table for use with this example.
CREATE TABLE dbo.SimpleTable (
ProductKey [INT] NOT NULL,
OrderDateKey [INT] NOT NULL,
DueDateKey [INT] NOT NULL,
ShipDateKey [INT] NOT NULL);
GO
--Create two nonclustered indexes for use with this example
CREATE INDEX nc1_simple ON dbo.SimpleTable (OrderDateKey);
CREATE INDEX nc2_simple ON dbo.SimpleTable (DueDateKey);
GO
SQL Server 2012 (11.x) と SQL Server 2014 (12.x) の場合のみ、列ストア インデックスを作成するには、非クラスター化インデックスを削除する必要があります。
DROP INDEX dbo.SimpleTable.nc1_simple;
DROP INDEX dbo.SimpleTable.nc2_simple;
--Convert the rowstore table to a columnstore index.
CREATE CLUSTERED COLUMNSTORE INDEX cci_simple ON dbo.SimpleTable;
GO
D. 大きいファクト テーブルを行ストアから列ストアに変換する
この例では、大きいファクト テーブルを行ストア テーブルから列ストア テーブルに変換する方法を説明します。
この例で使用する小さいテーブルを作成します。
--Create a rowstore table with a clustered index and a nonclustered index. CREATE TABLE dbo.MyFactTable ( ProductKey [INT] NOT NULL, OrderDateKey [INT] NOT NULL, DueDateKey [INT] NOT NULL, ShipDateKey [INT] NOT NULL INDEX IDX_CL_MyFactTable CLUSTERED ( ProductKey ) ); --Add a nonclustered index. CREATE INDEX my_index ON dbo.MyFactTable ( ProductKey, OrderDateKey );行ストア テーブルからすべての非クラスター化インデックスを削除します。 インデックスをスクリプト化して後で再作成することもできます。
--Drop all nonclustered indexes DROP INDEX my_index ON dbo.MyFactTable;行ストア テーブルを、クラスター化列ストア インデックスを持つ列ストア テーブルに変換します。
まず、既存のクラスター化行ストア インデックスの名前を検索します。 手順 1 では、インデックスの名前を
IDX_CL_MyFactTableに設定します。 インデックス名が指定されていない場合は、自動的に生成された一意のインデックス名が与えられています。 自動生成された名前は、次のサンプル クエリを使用して取得できます。SELECT i.object_id, i.name, t.object_id, t.name FROM sys.indexes i INNER JOIN sys.tables t ON i.object_id = t.object_id WHERE i.type_desc = 'CLUSTERED' AND t.name = 'MyFactTable';オプション 1: 既存のクラスター化インデックス
IDX_CL_MyFactTableを削除し、MyFactTableを列ストアに変換します。 新しいクラスター化列ストア インデックスの名前を変更します。--Drop the clustered rowstore index. DROP INDEX [IDX_CL_MyFactTable] ON dbo.MyFactTable; GO --Create a new clustered columnstore index with the name MyCCI. CREATE CLUSTERED COLUMNSTORE INDEX IDX_CCL_MyFactTable ON dbo.MyFactTable; GOオプション 2: 列ストアに変換し、既存の行ストア クラスター化インデックス名を再利用します。
--Create the clustered columnstore index, --replacing the existing rowstore clustered index of the same name CREATE CLUSTERED COLUMNSTORE INDEX [IDX_CL_MyFactTable] ON dbo.MyFactTable WITH (DROP_EXISTING = ON);
E. 列ストア テーブルを、クラスター化インデックスを持つ行ストア テーブルに変換する
列ストア テーブルをクラスター化インデックスを持つ行ストア テーブルに変換するには、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. 列ストア インデックスを再構成して最適化する
クラスター化列ストア インデックスの保守を行う 2 つの方法があります。 SQL Server 2016 (13.x) 以降では、REBUILD ではなく ALTER INDEX...REORGANIZE を使用します。 詳細については、列ストア インデックスの行グループに関するページを参照してください。 以前のバージョンの 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. 行ストア テーブルのセカンダリ インデックスとして列ストア インデックスを作成する
この例では、行ストア テーブルに非クラスター化列ストア インデックスを作成します。 このような状況では、1 つのみ列ストア インデックスを作成できます。 列ストア インデックスには、行ストア テーブルのデータのコピーが含まれているために、追加のストレージが必要です。 次の例では、単純なテーブルと列ストア クラスター化インデックスを作成します。その後、非クラスター化列ストア インデックスを作成する構文を示します。
CREATE TABLE dbo.SimpleTable (
ProductKey [INT] NOT NULL,
OrderDateKey [INT] NOT NULL,
DueDateKey [INT] NOT NULL,
ShipDateKey [INT] NOT NULL);
GO
CREATE CLUSTERED INDEX cl_simple ON dbo.SimpleTable (ProductKey);
GO
CREATE NONCLUSTERED COLUMNSTORE INDEX csindx_simple
ON dbo.SimpleTable (OrderDateKey, DueDateKey, ShipDateKey);
GO
B. すべてのオプションを使用して、基本的な非クラスター化列ストア インデックスを作成する
次の例は、DEFAULT ファイル グループに非クラスター化列ストア インデックスを作成し、最大並列度 (MAXDOP) を 2 に指定する構文を示しています。
CREATE NONCLUSTERED COLUMNSTORE INDEX csindx_simple
ON SimpleTable (OrderDateKey, DueDateKey, ShipDateKey)
WITH (DROP_EXISTING = ON,
MAXDOP = 2)
ON "DEFAULT";
GO
C. フィルター選択された述語で、非クラスター化列ストア インデックスを作成する
次の例では、AdventureWorks2022 サンプル データベースの Production.BillOfMaterials テーブルに、フィルター選択された非クラスター化列ストア インデックスを作成します。 フィルター述語では、フィルター選択されたインデックスに非キー列を含めることができます。 この例の述語では、EndDate が NULL 以外の行だけを選択します。
IF EXISTS (SELECT name FROM sys.indexes
WHERE name = N'FIBillOfMaterialsWithEndDate'
AND object_id = OBJECT_ID(N'Production.BillOfMaterials'))
DROP INDEX FIBillOfMaterialsWithEndDate
ON Production.BillOfMaterials;
GO
CREATE NONCLUSTERED COLUMNSTORE INDEX "FIBillOfMaterialsWithEndDate"
ON Production.BillOfMaterials (ComponentID, StartDate)
WHERE EndDate IS NOT NULL;
D. 非クラスター化列ストア インデックス内のデータを変更する
適用対象: SQL Server 2012 (11.x) から SQL Server 2014 (12.x)。
SQL Server 2014 (12.x) 以前のバージョンでは、テーブルに非クラスター化列ストア インデックスを作成した後、そのテーブル内のデータを直接変更することはできません。 INSERT、UPDATE、DELETE、または MERGE を使用するクエリは失敗し、エラー メッセージが返されます。 テーブル内のデータを追加または変更するために使用できるオプションを次に示します。
列ストア インデックスを無効にするか削除します。 その後、テーブル内のデータを更新できます。 列ストア インデックスを無効にした場合、データの更新の終了時に列ストア インデックスを再構築できます。 次に例を示します。
ALTER INDEX mycolumnstoreindex ON dbo.mytable DISABLE; -- update the data in mytable as necessary ALTER INDEX mycolumnstoreindex on dbo.mytable REBUILD;列ストア インデックスのないステージング テーブルにデータを読み込みます。 そのステージング テーブルに列ストア インデックスを構築します。 そのステージング テーブルをメイン テーブルの空のパーティションに切り替えます。
列ストア インデックスを持つテーブルから空のステージング テーブルにパーティションを切り替えます。 ステージング テーブルに列ストア インデックスがある場合は、列ストア インデックスを無効にします。 更新を実行します。 列ストア インデックスを構築 (または再構築) します。 ステージング テーブルを切り替えて、メイン テーブルの (空になった) パーティションに戻します。
例: Azure Synapse Analytics、Analytics Platform System (PDW)
A. クラスター化インデックスをクラスター化列ストア インデックスに変換する
DROP_EXISTING = ON で CREATE CLUSTERED COLUMNSTORE INDEX ステートメントを使用すると、次のことができます。
クラスター化インデックスをクラスター化列ストア インデックスに変換します。
クラスター化列ストア インデックスを再構築します。
次の例は、クラスター化インデックスを持つ行ストア テーブルとして xDimProduct テーブルを作成しています。 この例では次に、CREATE CLUSTERED COLUMNSTORE INDEX を使用して、テーブルを行ストア テーブルから列ストア テーブルに変更しています。
-- Uses AdventureWorks
IF EXISTS (SELECT name FROM sys.tables
WHERE name = N'xDimProduct'
AND object_id = OBJECT_ID (N'xDimProduct'))
DROP TABLE xDimProduct;
--Create a distributed table with a clustered index.
CREATE TABLE xDimProduct (ProductKey, ProductAlternateKey, ProductSubcategoryKey)
WITH ( DISTRIBUTION = HASH(ProductKey),
CLUSTERED INDEX (ProductKey) )
AS SELECT ProductKey, ProductAlternateKey, ProductSubcategoryKey FROM DimProduct;
sys.indexes を利用し、システム メタデータの新しいテーブルに自動作成されたクラスター化インデックスの名前を探します。 次に例を示します。
SELECT i.object_id, i.name, t.object_id, t.name, i.type_desc
FROM sys.indexes i
INNER JOIN sys.tables t ON i.object_id = t.object_id
WHERE i.type_desc = 'CLUSTERED'
AND t.name = 'xdimProduct';
次を選択できるようになります。
- 自動作成された名前を持つ既存のクラスター化列ストア インデックスを削除し、ユーザー定義名で新しいクラスター化列ストア インデックスを作成します。
- 既存のインデックスを削除し、クラスター化列ストア インデックスに変えます。
ClusteredIndex_1bd8af8797f7453182903cc68df48541のように、システム生成された同じ名前は維持します。
次に例を示します。
--1. DROP the existing clustered columnstore index with an automatically-created name, for example:
DROP INDEX ClusteredIndex_1bd8af8797f7453182903cc68df48541 on xdimProduct;
GO
CREATE CLUSTERED COLUMNSTORE INDEX [<new_index_name>]
ON xdimProduct;
GO
--Or,
--2. Change the existing clustered index to a clustered columnstore index with the same name.
CREATE CLUSTERED COLUMNSTORE INDEX [ClusteredIndex_1bd8af8797f7453182903cc68df48541]
ON xdimProduct
WITH ( DROP_EXISTING = ON );
GO
B. クラスター化列ストア インデックスを再構築する
この例は先の例を元に作られています。CREATE CLUSTERED COLUMNSTORE INDEX を使用し、cci_xDimProduct という名前の既存のクラスター化列ストア インデックスを再構築します。
--Rebuild the existing clustered columnstore index.
CREATE CLUSTERED COLUMNSTORE INDEX cci_xDimProduct
ON xdimProduct
WITH ( DROP_EXISTING = ON );
C. クラスター化列ストア インデックスの名前を変更する
クラスター化列ストア インデックスの名前を変更するには、既存のクラスター化列ストア インデックスを削除し、新しい名前でインデックスを再作成します。
この操作は、小さい、または空のテーブルに制限することをお勧めします。 大規模なクラスター化列ストア インデックスを削除し、別の名前で再構築すると長い時間がかかります。
次の例は、前の例の cci_xDimProduct クラスター化列ストア インデックスを参照しています。 この例では、cci_xDimProduct クラスター化列ストア インデックスを削除した後、mycci_xDimProduct の名前でクラスター化列ストア インデックスを再作成しています。
--For illustration purposes, drop the clustered columnstore index.
--The table continues to be distributed, but changes to a heap.
DROP INDEX cci_xdimProduct ON xDimProduct;
--Create a clustered index with a new name, mycci_xDimProduct.
CREATE CLUSTERED COLUMNSTORE INDEX mycci_xDimProduct
ON xdimProduct
WITH ( DROP_EXISTING = OFF );
D. 列ストア テーブルを、クラスター化インデックスを持つ行ストア テーブルに変換する
クラスター化列ストア インデックスを削除し、クラスター化インデックスを作成するという状況もあります。 クラスター化列ストア インデックスを削除すると、テーブルは行ストア形式に変更されます。 この例では、クラスター化インデックスが含まれる行ストア テーブルに列ストア テーブルを変換します。同じ名前が使用されます。 データは何も失われません。 すべてのデータが行ストア テーブルに移動し、一覧表示されている列がクラスター化インデックスのキー列になります。
--Drop the clustered columnstore index and create a clustered rowstore index.
--All of the columns are stored in the rowstore clustered index.
--The columns listed are the included columns in the index.
CREATE CLUSTERED INDEX cci_xDimProduct
ON xdimProduct (ProductKey, ProductAlternateKey, ProductSubcategoryKey, WeightUnitMeasureCode)
WITH ( DROP_EXISTING = ON);
E. 列ストア テーブルを行ストア ヒープに戻す
DROP INDEX (SQL Server PDW) を使用し、クラスター化列ストア インデックスを削除し、テーブルを行ストア ヒープに変換します。 この例では、cci_xDimProduct テーブルを行ストア ヒープに変換します。 テーブルは引き続き配布されますが、ヒープとして保存されます。
--Drop the clustered columnstore index. The table continues to be distributed, but changes to a heap.
DROP INDEX cci_xdimProduct ON xdimProduct;
F. インデックスのないテーブル上で順序付けされたクラスター化列ストア インデックスを作成する
列リストを指定しなくても、順序付けされていない列ストア インデックスは、既定ですべての列を対象とします。 順序付けされた列ストア インデックスを使用すると、列の順序を指定できます。 リストにすべての列を含める必要はありません。
順序付き列ストア インデックスは、Azure Synapse Analytics、Analytics Platform System (PDW)、SQL Server 2022 (16.x) で使用できます。 詳細については、「順序指定クラスター化列ストア インデックスを使用したパフォーマンスのチューニング」を参照してください。
CREATE CLUSTERED COLUMNSTORE INDEX cci ON Sales.OrderLines
ORDER (SHIPDATE);
G. クラスター化列ストア インデックスを順序付けされたクラスター化列ストア インデックスに変換する
CREATE CLUSTERED COLUMNSTORE INDEX cci ON Sales.OrderLines
ORDER (SHIPDATE)
WITH (DROP_EXISTING = ON);
H. 順序付けされたクラスター化列ストア インデックスの順序に列を追加する
Azure Synapse Analytics、Analytics Platform System (PDW)、SQL Server 2022 (16.x) 以降では、列ストア インデックス内の列の順序を指定できます。 元の順序付けされたクラスター化列ストア インデックスは、SHIPDATE 列でのみ順序付けされました。 次の例では、PRODUCTKEY 列を順序付けに追加します。
CREATE CLUSTERED COLUMNSTORE INDEX cci ON Sales.OrderLines
ORDER (SHIPDATE, PRODUCTKEY)
WITH (DROP_EXISTING = ON);
I. 順序付けされた列の序数を変更する
元の順序付けされたクラスター化列ストア インデックスは、SHIPDATE、PRODUCTKEY で順序付けされました。 次の例では、順序を PRODUCTKEY､SHIPDATE に変更します。
CREATE CLUSTERED COLUMNSTORE INDEX cci ON Sales.OrderLines
ORDER (PRODUCTKEY,SHIPDATE)
WITH (DROP_EXISTING = ON);
J. 順序付けされたクラスター化列ストア インデックスを作成する
適用対象: Azure Synapse Analytics および SQL Server 2022 (16.x)
順序付けキーを使用して、順序指定クラスター化列ストア インデックスを作成できます。 順序付けされたクラスター化列ストア インデックスを作成する場合は、クエリ ヒント MAXDOP = 1 を適用して、並べ替えの品質を最大限に高め、最短の期間にする必要があります。
CREATE CLUSTERED COLUMNSTORE INDEX [OrderedCCI] ON dbo.FactResellerSalesPartCategoryFull
ORDER (EnglishProductSubcategoryName, EnglishProductName)
WITH (MAXDOP = 1, DROP_EXISTING = ON);
関連するコンテンツ
[ 列ストア インデックス: 概要](https://docs.microsoft.com/sql/relational-databases/indexes/columnstore-indexes-overview?view=sql-server-ver15) - 列ストア インデックスの新機能
- 列ストアを使用したリアルタイム運用分析の概要
- 列ストア インデックス - データ ウェアハウス
[ 列ストア インデックス - クエリ パフォーマンス](https://docs.microsoft.com/sql/relational-databases/indexes/columnstore-indexes-query-performance?view=sql-server-ver15) [ 列ストア インデックス - 設計ガイダンス](https://docs.microsoft.com/sql/relational-databases/indexes/columnstore-indexes-design-guidance?view=sql-server-ver15)
フィードバック
以下は間もなく提供いたします。2024 年を通じて、コンテンツのフィードバック メカニズムとして GitHub の issue を段階的に廃止し、新しいフィードバック システムに置き換えます。 詳細については、「https://aka.ms/ContentUserFeedback」を参照してください。
フィードバックの送信と表示