CREATE STATISTICS (Transact-SQL)

项目
05/23/2024

适用于：SQL Server Azure SQL 数据库 Azure SQL 托管实例 Azure Synapse Analytics Microsoft Fabric 中的 SQL 分析终结点 Microsoft Fabric 中的仓库

在表格、索引视图或外部表格的一列或多列上创建查询优化统计信息。对于大多数查询，查询优化器已为高质量查询计划生成必要的统计信息;在某些情况下，需要创建额外的统计信息， CREATE STATISTICS 或修改查询设计以提高查询性能。

若要了解更多信息，请参阅统计信息。

注意

有关 Microsoft Fabric 中的统计信息的详细信息，请参阅 Fabric 数据仓库中的统计信息。

Transact-SQL 语法约定

语法

SQL Server、Azure SQL 数据库和Azure SQL 托管实例的语法。

-- 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 ]

Azure Synapse Analytics 和分析平台系统的语法（PDW）。

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 | = | <> | != | > | >= | !> | < | <= | !<

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
      }
    ]
[ ; ]

注意

若要查看 SQL Server 2014 (12.x) 及更早版本的 Transact-SQL 语法，请参阅早期版本文档。

参数

statistics_name

要创建的统计信息的名称。

table_or_indexed_view_name

要在其中创建统计信息的表、索引视图或外部表的名称。若要在另一个数据库中创建统计信息，请指定限定的表名称。

column [ ,...n]

统计信息中要包含的一列或多列。这些列按照从左到右的优先顺序排列。仅第一列用于创建直方图。所有列都可用于调用密度的跨列相关性统计信息。

您可以指定任何可指定为索引键列的列，但下列情况除外：

不能指定 xml、全文和 FILESTREAM 列。
仅当计算列和QUOTED_IDENTIFIER数据库设置为ON时ARITHABORT，才能指定计算列。
如果 CLR 用户定义类型支持二进制排序，则可以指定 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） SP 1 CU 4、SQL Server 2017 （14.x） CU 1 及更高版本

扫描所有行，计算统计信息。 FULLSCAN 并 SAMPLE 100 PERCENT 具有相同的结果。 FULLSCAN 不能与该选项一起使用 SAMPLE 。

如果省略，SQL Server 会使用采样创建统计信息，并确定创建高质量查询计划所需的示例大小。

在 Microsoft Fabric 中的 Warehouse 中，仅支持单列 FULLSCAN 和基于单列 SAMPLE的统计信息。如果未包含任何选项， SAMPLE 则会创建统计信息。

SAMPLE number { PERCENT | ROWS }

指定查询优化器在创建统计信息时要使用的表或索引视图中的近似百分比或行数。对于PERCENT，数字可以是 0 到 100，对于ROWS，数字可以是 0 到总行数。查询优化器抽样的实际行百分比或行数可能与指定的行百分比或行数不匹配。例如，查询优化器扫描数据页上的所有行。

SAMPLE 对于基于默认采样的查询计划不是最佳情况的特殊情况非常有用。在大多数情况下，不需要指定 SAMPLE ，因为查询优化器已使用采样，并默认确定统计显著样本大小，因为需要创建高质量的查询计划。

SAMPLE 不能与 FULLSCAN 选项一起使用。指定或FULLSCAN未指定时SAMPLE，查询优化器使用采样数据，并默认计算样本大小。

我们建议不指定 0 PERCENT 或 0 ROWS。在创建或0 ROWS指定统计信息对象时0 PERCENT，但不包含统计信息数据。

在 Microsoft Fabric 中的 Warehouse 中，仅支持单列 FULLSCAN 和基于单列 SAMPLE的统计信息。如果未包含任何选项， FULLSCAN 则会创建统计信息。

PERSIST_SAMPLE_PERCENT = { ON | OFF }

当 ON，统计信息保留未显式指定采样百分比的后续更新的创建采样百分比。当 OFF统计信息采样百分比重置为未显式指定采样百分比的后续更新中的默认采样。默认为 OFF。

注意

如果该表被截断，则截断的 HoBT 上生成的所有统计信息将恢复为使用默认采样百分比。

STATS_STREAM = stats_stream

标识为仅供参考。不支持。不保证以后的兼容性。

NORECOMPUTE

为statistics_name禁用自动统计信息更新选项AUTO_STATISTICS_UPDATE。如果指定了该选项，则查询优化器将完成 statistics_name 的任何正在进行中的统计信息更新并禁止在将来出现更新。

若要重新启用统计信息更新，请使用 DROP STATISTICS 删除统计信息，然后运行而不NORECOMPUTE使用CREATE STATISTICS此选项。

警告

如果禁用统计信息自动更新，它可能会阻止查询优化器为涉及表的查询选取最佳执行计划。应谨慎使用此选项，并且仅由限定的数据库管理员使用。

有关选项的详细信息AUTO_STATISTICS_UPDATE，请参阅 ALTER DATABA标准版标准版T 选项。有关禁用和重新启用统计信息更新的详细信息，请参阅统计信息。

INCREMENTAL = { ON | OFF }

适用于：SQL Server 2014 (12.x) 及更高版本

创建统计信息时 ON，按分区统计信息创建。当 OFF，所有分区的统计信息组合在一起。默认为 OFF。

如果每个分区统计信息不受支持，则会生成错误。对于以下统计信息类型，不支持增量统计信息：

使用与基表不分区对齐的索引创建的统计信息。
对 Always On 可读辅助数据库创建的统计信息。
对只读数据库创建的统计信息。
对筛选的索引创建的统计信息。
对视图创建的统计信息。
对内部表创建的统计信息。
使用空间索引或 XML 索引创建的统计信息。

MAXDOP = max_degree_of_parallelism

适用于：SQL Server 2016 （13.x） SP 2、SQL Server 2017 （14.x） CU 3 及更高版本

在 统计信息操作期间重写最大并行度 配置选项。有关详细信息，请参阅配置最大并行度（服务器配置选项）。用于 MAXDOP 限制并行计划执行中使用的处理器数。最大数量为 64 个处理器。

max_degree_of_parallelism 可以是：

1：取消并行计划生成。
>1：将并行索引操作中使用的最大处理器数限制为指定的数字。
0 （默认值）：根据当前系统工作负荷使用实际处理器数或更少。

update_stats_stream_option

标识为仅供参考。不支持。不保证以后的兼容性。

AUTO_DROP = { ON | OFF }

适用于：SQL Server 2022（16.x）及更高版本，以及Azure SQL 数据库、Azure SQL 托管实例

在 SQL Server 2022（16.x）之前，如果用户或第三方工具在用户数据库上手动创建统计信息，则这些统计信息对象可能会阻止或干扰客户可能需要的架构更改。

从 SQL Server 2022（16.x 开始），该 AUTO_DROP 选项默认在所有新的和已迁移的数据库上启用。该AUTO_DROP属性允许在模式下创建统计信息对象，以便统计信息对象不会阻止后续架构更改，而是根据需要删除统计信息。这样，手动创建的统计信息和 AUTO_DROP 启用的统计信息的行为类似于自动创建统计信息。

注意

尝试在自动创建的统计信息上设置或取消设置 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 命令一列的统计信息。

具有筛选条件的统计信息

筛选统计信息可以提高以下从定义完善的数据子集选择数据的查询的查询性能。筛选的统计信息在 WHERE 子句中使用筛选谓词来选择统计信息中包含的数据子集。

何时使用 CREATE STATISTICS

有关何时使用 CREATE STATISTICS 的详细信息，请参阅统计信息。

筛选统计信息的引用依赖项

sys.sql_expression_dependencies 目录视图将筛选统计信息谓词中的每一列作为一个引用依赖项，进行跟踪。在创建筛选统计信息之前，请考虑对表列执行的操作。不能删除、重命名或更改在筛选的统计信息谓词中定义的表列的定义。

限制

外部表格中不支持更新统计信息。若要更新外部表格中的统计信息，请删除并重新创建统计信息。
每个统计信息对象至多可列出 64 列。
该MAXDOP选项与选项不兼容，ROWCOUNT并且PAGECOUNT与选项不兼容STATS_STREAM。
如果使用的话，MAXDOP 选项会受资源调控器工作负载组 MAX_DOP 设置的限制。
CREATEAzure SQL 数据库不支持外部DROP STATISTICS表。

示例

本文中的 Transact-SQL 代码示例使用 AdventureWorks2022 示例数据库，可从 Microsoft SQL Server 示例和社区项目主页下载它。

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. 在外部表上创建统计信息

在外部表上创建统计信息时，除了提供列的列表，唯一需要做的决定是通过对行采样创建统计数据，还是通过扫描所有行创建统计数据。 CREATEAzure SQL 数据库不支持外部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 一起使用

以下示例为表和EmailPromotion列中Person的所有行BusinessEntityID创建NamePurchase统计信息，并为未显式指定采样百分比的所有后续更新设置 100% 的采样百分比。

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

使用 AdventureWorksDW 数据库的示例

F. 在两列中创建统计信息

以下示例基于 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. 将 CREATE STATISTICS 与 AUTO_DROP 配合使用

要使用自动删除统计信息，只需将以下内容添加到统计信息创建或更新的“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;

Share via

CREATE STATISTICS (Transact-SQL)

语法

参数

statistics_name

table_or_indexed_view_name

column [ ,...n]

WHERE <filter_predicate>

FULLSCAN

SAMPLE number { PERCENT | ROWS }

PERSIST_SAMPLE_PERCENT = { ON | OFF }

STATS_STREAM = stats_stream

NORECOMPUTE

INCREMENTAL = { ON | OFF }

MAXDOP = max_degree_of_parallelism

update_stats_stream_option

AUTO_DROP = { ON | OFF }

权限

注解

外部表的统计信息

具有筛选条件的统计信息

何时使用 CREATE STATISTICS

筛选统计信息的引用依赖项

限制

示例

A. 将 CREATE STATISTICS 与 SAMPLE number PERCENT 一起使用

B. 将 CREATE STATISTICS 与 FULLSCAN 和 NORECOMPUTE 一起使用

C. 使用 CREATE STATISTICS 创建筛选统计信息

D. 在外部表上创建统计信息

E. 将 CREATE STATISTICS 与 FULLSCAN 和 PERSIST_SAMPLE_PERCENT 一起使用

使用 AdventureWorksDW 数据库的示例

F. 在两列中创建统计信息

G. 使用完全扫描创建统计信息

H. 指定采样百分比，创建统计信息

I. 将 CREATE STATISTICS 与 AUTO_DROP 配合使用

反馈

反馈

其他资源

Share via

CREATE STATISTICS (Transact-SQL)

语法

参数

statistics_name

table_or_indexed_view_name

column [ ,...n]

WHERE <filter_predicate>

FULLSCAN

SAMPLE number { PERCENT | ROWS }

PERSIST_SAMPLE_PERCENT = { ON | OFF }

STATS_STREAM = stats_stream

NORECOMPUTE

INCREMENTAL = { ON | OFF }

MAXDOP = max_degree_of_parallelism

update_stats_stream_option

AUTO_DROP = { ON | OFF }

权限

注解

外部表的统计信息

具有筛选条件的统计信息

何时使用 CREATE STATISTICS

筛选统计信息的引用依赖项

限制

示例

A. 将 CREATE STATISTICS 与 SAMPLE number PERCENT 一起使用

B. 将 CREATE STATISTICS 与 FULLSCAN 和 NORECOMPUTE 一起使用

C. 使用 CREATE STATISTICS 创建筛选统计信息

D. 在外部表上创建统计信息

E. 将 CREATE STATISTICS 与 FULLSCAN 和 PERSIST_SAMPLE_PERCENT 一起使用

使用 AdventureWorksDW 数据库的示例

F. 在两列中创建统计信息

G. 使用完全扫描创建统计信息

H. 指定采样百分比，创建统计信息

I. 将 CREATE STATISTICS 与 AUTO_DROP 配合使用

相关内容

反馈

反馈

其他资源