CREATE STATISTICS (Transact-SQL)

適用対象:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsMicrosoft Fabric の SQL 分析エンドポイントMicrosoft Fabric のウェアハウス

テーブル、インデックス付きビュー、または、外部テーブルの 1 つまたは複数の列に関するクエリ最適化の統計を作成します。 ほとんどのクエリでは、高品質のクエリ プランに必要な統計がクエリ オプティマイザーによって既に生成されていますが、クエリのパフォーマンスを向上させるために CREATE STATISTICS で追加の統計を作成したりクエリのデザインを変更したりする必要がある場合もあります。

詳しくは、「統計」をご覧ください。

注意

Microsoft Fabric の統計の詳細については、「Microsoft Fabric の統計」を参照してください。

Transact-SQL 構文表記規則

構文

-- Syntax for SQL Server and Azure SQL Database
-- Create statistics on an external table

CREATE STATISTICS statistics_name
ON { table_or_indexed_view_name } ( column [ ,...n ] )
    [ WITH FULLSCAN ] ;
  
-- Create statistics on a regular table or indexed view
CREATE STATISTICS statistics_name
ON { table_or_indexed_view_name } ( column [ ,...n ] )
    [ WHERE <filter_predicate> ]
    [ WITH
        [ [ FULLSCAN
            [ [ , ] PERSIST_SAMPLE_PERCENT = { ON | OFF } ]
          | SAMPLE number { PERCENT | ROWS }
            [ [ , ] PERSIST_SAMPLE_PERCENT = { ON | OFF } ]
          | <update_stats_stream_option> [ ,...n ]
        [ [ , ] NORECOMPUTE ]
        [ [ , ] INCREMENTAL = { ON | OFF } ]
        [ [ , ] MAXDOP = max_degree_of_parallelism ]
        [ [ , ] AUTO_DROP = { ON | OFF } ]
    ] ;
  
<filter_predicate> ::=
    <conjunct> [AND <conjunct>]
  
<conjunct> ::=
    <disjunct> | <comparison>
  
<disjunct> ::=
        column_name IN (constant ,...)
  
<comparison> ::=
        column_name <comparison_op> constant
  
<comparison_op> ::=
    IS | IS NOT | = | <> | != | > | >= | !> | < | <= | !<
    
<update_stats_stream_option> ::=
    [ STATS_STREAM = stats_stream ]
    [ ROWCOUNT = numeric_constant ]
    [ PAGECOUNT = numeric_contant ]

-- Syntax for Azure Synapse Analytics and Parallel Data Warehouse 
  
CREATE STATISTICS statistics_name
    ON { database_name.schema_name.table_name | schema_name.table_name | table_name }
    ( column_name  [ ,...n ] )
    [ WHERE <filter_predicate> ]
    [ WITH {
           FULLSCAN
           | SAMPLE number PERCENT
      }
    ]
[;]
  
<filter_predicate> ::=
    <conjunct> [AND <conjunct>]
  
<conjunct> ::=
    <disjunct> | <comparison>
  
<disjunct> ::=
        column_name IN (constant ,...)
  
<comparison> ::=
        column_name <comparison_op> constant
  
<comparison_op> ::=
    IS | IS NOT | = | <> | != | > | >= | !> | < | <= | !<

-- Syntax for Microsoft Fabric
CREATE STATISTICS statistics_name
    ON { database_name.schema_name.table_name | schema_name.table_name | table_name }
    ( column_name )
    [ WITH {
           FULLSCAN
           | SAMPLE number PERCENT
      }
    ]
[;]

Note

SQL Server 2014 (12.x) 以前のバージョンの Transact-SQL 構文を確認するには、以前のバージョンのドキュメントを参照してください。

引数

statistics_name

作成する統計の名前です。

table_or_indexed_view_name

統計を作成するテーブル、インデックス付きビュー、または外部テーブルの名前です。 別のデータベースの統計を作成するには、修飾テーブル名を指定します。

column [ ,...n]

統計に含める 1 つまたは複数の列。 列は、左から右への優先順位にする必要があります。 ヒストグラムの作成には最初の列のみが使用されます。 すべての列は、密度と呼ばれる列間の相関統計に使用されます。

インデックス キー列として指定できる任意の列を指定できますが、次の例外があります。

• XML 列、フルテキスト列、FILESTREAM 列は指定できません。

• 計算列は、データベース設定の ARITHABORT と QUOTED_IDENTIFIER が ON に設定されている場合にのみ指定できます。

• CLR ユーザー定義型の列は、データ型でバイナリ順がサポートされている場合に指定できます。 ユーザー定義型列のメソッド呼び出しとして定義されている計算列は、メソッドが決定的とマークされている場合に指定できます。

WHERE <filter_predicate>

統計オブジェクトを作成するときに含める行のサブセットを選択するための式を指定します。 フィルター述語を使用して作成された統計は、フィルター選択された統計情報と呼ばれます。 フィルター述語には単純な比較ロジックを使用するので、計算列、UDT 列、空間データ型列、または hierarchyID データ型列を参照することはできません。 比較演算子では、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

フィルター述語について詳しくは、「フィルター選択されたインデックスの作成」をご覧ください。

FULLSCAN

適用対象: SQL Server 2016 (13.x) (SQL Server 2016 (13.x) SP1 CU4 以降) 以降 (SQL Server 2017 (14.x) CU1 以降)

すべての行をスキャンして統計を計算します。 FULLSCAN と SAMPLE 100 PERCENT は同じ結果になります。 SAMPLE オプションには FULLSCAN を使用できません。

省略すると、SQL Server ではサンプリングを使用して統計が作成され、高品質のクエリ プランを作成するために必要なサンプル サイズが決定されます。

Microsoft Fabric のウェアハウスでは、単一列の FULLSCAN と単一列の SAMPLE ベースの統計のみがサポートされます。 オプションが指定されない場合、FULLSCAN 統計が作成されます。

SAMPLE number { PERCENT | ROWS }

テーブルやインデックス付きビューに含まれている行について、クエリ オプティマイザーで統計を作成する際に使用するおおよその割合または数を指定します。 PERCENT の場合、number には 0 ～ 100 を指定します。ROWS の場合、number には 0 ～合計行数を指定します。 クエリ オプティマイザーによってサンプリングされる行の実際の割合や行数が、指定した割合や行数と一致しない場合もあります。 たとえば、データ ページではすべての行がスキャンされます。

SAMPLE は、既定のサンプリングに基づくクエリ プランが最適ではない特殊な場合に使用できます。 クエリ オプティマイザーによって既にサンプリングが使用され、既定で統計的に有意なサンプル サイズが決定されるため、ほとんどの場合は SAMPLE を指定する必要がありませんが、高品質のクエリ プランを作成する場合は必要です。

FULLSCAN オプションには SAMPLE を使用できません。 SAMPLE も FULLSCAN も指定しない場合、既定でサンプリングしたデータが使用され、サンプル サイズが計算されます。

0 PERCENT や 0 ROWS を指定することはお勧めしません。 0 PERCENT または 0 ROWS を指定した場合、統計オブジェクトは作成されますが、統計データは含まれません。

Microsoft Fabric のウェアハウスでは、単一列の FULLSCAN と単一列の SAMPLE ベースの統計のみがサポートされます。 オプションが指定されない場合、FULLSCAN 統計が作成されます。

PERSIST_SAMPLE_PERCENT = { ON | OFF }

ON の場合、統計では、サンプリング率が明示的に指定されていない後続の更新で作成サンプリング率が保持されます。 OFF の場合、統計サンプリング率は、サンプリング率が明示的に指定されていない後続の更新で既定のサンプリングにリセットされます。 既定値は OFF です。

Note

テーブルが切り捨てられた場合、切り捨てられた HoBT に基づいて作成されたすべての統計は、既定のサンプリング率を使用するように戻されます。

STATS_STREAM = stats_stream

単に情報を示すためだけに特定されます。 サポートされていません。 将来の互換性は保証されません。

NORECOMPUTE

statistics_name の自動統計更新オプション (AUTO_STATISTICS_UPDATE) を無効にします。 このオプションを指定すると、statistics_name の進行中の更新は最後まで実行され、その後の更新が無効になります。

統計の更新を再有効化するには、DROP STATISTICS で統計を削除してから、NORECOMPUTE オプションを指定せずに CREATE STATISTICS を実行します。

警告

このオプションを使用すると、最適ではないクエリ プランが作成されることがあります。 このオプションは慎重に使用してください。特に、資格のあるシステム管理者だけが使用することをお勧めします。

AUTO_STATISTICS_UPDATE オプションの詳細については、「ALTER DATABASE の SET オプション (Transact-SQL)」を参照してください。 統計の更新の無効化および再有効化について詳しくは、「統計」をご覧ください。

INCREMENTAL = { ON | OFF }

適用対象: SQL Server 2014 (12.x) 以降。

ON の場合、作成される統計はパーティションごとの統計です。 OFF の場合、すべてのパーティションの統計が結合されます。 既定値は OFF です。

パーティションごとの統計がサポートされていない場合は、エラーが生成されます。 次の種類の統計では、増分統計がサポートされていません。

• ベース テーブルにパーティションで固定されていないインデックスを使用して作成された統計。
• Always On の読み取り可能なセカンダリ データベースに対して作成された統計。
• 読み取り専用のデータベースに対して作成された統計。
• フィルター選択されたインデックスに対して作成された統計。
• ビューに対して作成された統計。
• 内部テーブルに対して作成された統計。
• 空間インデックスまたは XML インデックスを使用して作成された統計。

MAXDOP = max_degree_of_parallelism

適用対象:SQL Server (SQL Server 2016 (13.x) SP2 および SQL Server 2017 (14.x) CU3 以降)。

統計操作の間、max degree of parallelism 構成オプションをオーバーライドします。 詳細については、「 max degree of parallelism サーバー構成オプションの構成」を参照してください。 並列プランの実行で使用されるプロセッサ数を制限するには、MAXDOP を使用します。 最大数は 64 プロセッサです。

max_degree_of_parallelism は次のように指定できます。

1
並列プラン生成を抑制します。

>1
現在のシステム ワークロードに基づいて、並列統計操作で使用される最大プロセッサ数を指定の数以下に制限します。

0 (既定値)
現在のシステム ワークロードに基づいて、実際の数以下のプロセッサを使用します。

update_stats_stream_option

単に情報を示すためだけに特定されます。 サポートされていません。 将来の互換性は保証されません。

AUTO_DROP = { ON | OFF }

適用対象: Azure SQL Database、Azure SQL Managed Instance、SQL Server 2022 (16.x) 以降

SQL Server 2022 (16.x) より前は、統計がユーザーによって手動で、またはユーザー データベース上にあるサード パーティ製ツールによって作成される場合、その統計オブジェクトを使用すると、顧客が目的とするスキーマの変更が、ブロックされたり、干渉されたりする可能性があります。

SQL Server 2022 (16.x) 以降の AUTO_DROP オプションは、新しいデータベースと移行されたすべてのデータベースで既定で有効になります。 AUTO_DROP プロパティを使用すると、モード内での統計オブジェクトの作成が可能になり、後続のスキーマの変更が統計オブジェクトによってブロック "されなくなります"。ただし、代わりに、必要に応じて統計が削除されます。 このように、AUTO_DROP が有効になっている手動で作成された統計は、自動作成された統計と同様に動作します。

Note

自動作成された統計で Auto_Drop プロパティを設定または設定解除しようとすると、エラーが発生する可能性があります。 自動作成された統計では、常に自動削除が使用されます。 一部のバックアップでは、復元時に、統計オブジェクトが次回更新 (手動または自動) されるまで、このプロパティが正しく設定されないことがあります。 ただし、自動作成された統計は、自動削除の統計と同様に動作します。 以前のバージョンから SQL Server 2022 (16.x) にデータベースを復元する場合は、データベースで sp_updatestats を実行し、統計の AUTO_DROP 機能の適切なメタデータを設定することをお勧めします。

詳細については、「AUTO_DROP オプション」を参照してください。

アクセス許可

これらのアクセス許可のいずれかが必要です。

• ALTER TABLE
• ユーザーがテーブルの所有者です
• db_ddladmin 固定データベース ロールのメンバーシップ

解説

SQL Server で tempdb を使用すると、統計を構築する前に、サンプリングされた行を並べ替えることができます。

外部テーブルの統計

外部テーブルの統計を作成する場合は、SQL Server を使用すると、外部テーブルが SQL Server の一時テーブルにインポートされ、統計が作成されます。 サンプルの統計の場合は、サンプリングされた行のみがインポートされます。 大規模な外部テーブルがある場合は、フル スキャン オプションではなく既定のサンプリングを使用する方がはるかに高速です。

外部テーブルがデータ型として DELIMITEDTEXT、CSV、PARQUET、または DELTA を使用している場合、外部テーブルは CREATE STATISTICS コマンドごとに 1 列の統計のみをサポートします。　

条件がフィルター選択された統計情報

適切に定義されたデータのサブセットから選択するクエリでは、フィルター選択された統計情報を使用するとクエリのパフォーマンスを向上させることができます。 フィルター選択された統計情報では、統計情報に含まれるデータのサブセットを選択するために WHERE 句でフィルター述語を使用します。

CREATE STATISTICS を使用する場合

CREATE STATISTICS を使用する場合について詳しくは、「統計」をご覧ください。

フィルター選択された統計の依存関係を参照する

sys.sql_expression_dependencies カタログ ビューでは、フィルター選択された統計情報の述語の各列を、参照による依存関係として追跡します。 フィルター選択された統計情報の述語で定義されているテーブル列の定義を削除、名前変更、または変更することはできないので、フィルター選択された統計情報を作成する前に、テーブル列で実行する操作を検討してください。

制限事項と制約事項

• テーブルの外部では、統計を更新することはできません。 外部テーブルの統計を更新するには、統計を削除して再作成します。
• 統計オブジェクトごとに最大 64 列の一覧を取得できます。
• MAXDOP オプションは、STATS_STREAM、ROWCOUNT、PAGECOUNT オプションと互換性がありません。
• MAXDOP オプションは、Resource Governor ワークロード グループの MAX_DOP の設定によって制限されます (使用されている場合)。
• Azure SQL Database では、外部テーブルの CREATE と DROP STATISTICS はサポートされていません。

例

例では、AdventureWorks データベースを使用します。

A. CREATE STATISTICS を SAMPLE number PERCENT と共に使用する

次の例では、AdventureWorks2022 データベースの Person テーブルの BusinessEntityID 列と EmailPromotion 列の 5% のランダムなサンプルを使って、ContactMail1 統計を作成します。

CREATE STATISTICS ContactMail1
    ON Person.Person (BusinessEntityID, EmailPromotion)
    WITH SAMPLE 5 PERCENT;

B. CREATE STATISTICS を FULLSCAN および NORECOMPUTE と共に使用する

次の例では、NamePurchase テーブルの BusinessEntityID 列と EmailPromotion 列のすべての行を対象に、Person 統計情報を作成し、統計の自動再計算を無効にします。

CREATE STATISTICS NamePurchase
    ON AdventureWorks2022.Person.Person (BusinessEntityID, EmailPromotion)
    WITH FULLSCAN, NORECOMPUTE;

C. CREATE STATISTICS を使用してフィルター選択された統計を作成する

次の例では、フィルター選択された統計情報 ContactPromotion1 を作成します。 データベース エンジンによって、データの 50% がサンプリングされた後、EmailPromotion が 2 に等しい行が選択されます。

CREATE STATISTICS ContactPromotion1
    ON Person.Person (BusinessEntityID, LastName, EmailPromotion)
WHERE EmailPromotion = 2
WITH SAMPLE 50 PERCENT;
GO

D. 外部テーブルの統計を作成する

列の一覧を指定する以外に、外部テーブルの統計を作成するときに必要な決定事項は、統計を作成する際に行をサンプリングするか、すべての行をスキャンするかという点のみです。 Azure SQL Database では、外部テーブルの CREATE と DROP STATISTICS はサポートされていません。

SQL Server によってデータが外部テーブルから一時テーブルにインポートされて統計が作成されるため、フル スキャンのオプションを実行する場合は時間がかかります。 大きなテーブルの場合、通常は既定のサンプリング方法で十分です。

--Create statistics on an external table and use default sampling.
CREATE STATISTICS CustomerStats1 ON DimCustomer (CustomerKey, EmailAddress);
  
--Create statistics on an external table and scan all the rows
CREATE STATISTICS CustomerStats1 ON DimCustomer (CustomerKey, EmailAddress) WITH FULLSCAN;

E. CREATE STATISTICS を FULLSCAN および PERSIST_SAMPLE_PERCENT と共に使用する

次の例では、Person テーブルの BusinessEntityID 列と EmailPromotion 列のすべての行について NamePurchase 統計を作成し、サンプリング率を明示的に指定しない後続のすべての更新について 100 パーセントのサンプリング率を設定します。

CREATE STATISTICS NamePurchase
    ON AdventureWorks2022.Person.Person (BusinessEntityID, EmailPromotion)
    WITH FULLSCAN, PERSIST_SAMPLE_PERCENT = ON;

AdventureWorksDW データベースの使用例

F. 2 つの列の統計を作成する

次の例では、DimCustomer テーブルの CustomerKey 列と EmailAddress 列に基づいて、CustomerStats1 統計を作成します。 統計は、Customer テーブルの行の統計的に優位なサンプリングに基づいて作成されます。

CREATE STATISTICS CustomerStats1 ON DimCustomer (CustomerKey, EmailAddress);

G. フル スキャンを使用して統計を作成する

次の例では、DimCustomer テーブルのすべての行のスキャンに基づいて CustomerStatsFullScan 統計を作成します。

CREATE STATISTICS CustomerStatsFullScan
ON DimCustomer (CustomerKey, EmailAddress) WITH FULLSCAN;

H. サンプル率を指定して統計を作成する

次の例では、DimCustomer テーブルの行の 50 パーセントのスキャンに基づいて CustomerStatsSampleScan 統計を作成します。

CREATE STATISTICS CustomerStatsSampleScan
ON DimCustomer (CustomerKey, EmailAddress) WITH SAMPLE 50 PERCENT;

I. AUTO_DROP で CREATE STATISTICS を使用する

自動削除の統計を使用するには、統計の作成または更新の "WITH" 句に、単に次を追加します。

CREATE STATISTICS CustomerStats1 ON DimCustomer (CustomerKey, EmailAddress) WITH AUTO_DROP = ON

既存の統計の自動削除設定を評価するには、sys.stats の auto_drop 列を使用します。

SELECT object_id, [name], auto_drop
FROM sys.stats;

次のステップ

• 統計
• Microsoft Fabric の統計
• UPDATE STATISTICS (Transact-SQL)
• sp_updatestats (Transact-SQL)
• DBCC SHOW_STATISTICS (Transact-SQL)
• DROP STATISTICS (Transact-SQL)
• sys.stats (Transact-SQL)
• sys.stats_columns (Transact-SQL)