Skip to content

sys-stats-transact-sql.md

Latest commit

 

History

History
97 lines (80 loc) · 8.23 KB

sys-stats-transact-sql.md

File metadata and controls

97 lines (80 loc) · 8.23 KB
title sys.stats (Transact-SQL)
description The sys.stats system catalog view contains a row for each statistics object that exists for the tables, indexes, and indexed views in a SQL Server database.
author rwestMSFT
ms.author randolphwest
ms.date 10/02/2025
ms.service sql
ms.subservice system-objects
ms.topic reference
ms.custom
ignite-2025
f1_keywords
sys.stats
stats_TSQL
sys.stats_TSQL
stats
helpviewer_keywords
sys.stats catalog view
dev_langs
TSQL
monikerRange >=aps-pdw-2016 || =azuresqldb-current || =azure-sqldw-latest || >=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current || =fabric || =fabric-sqldb

sys.stats (Transact-SQL)

[!INCLUDE sql-asdb-asdbmi-asa-pdw-fabricse-fabricdw-fabricsqldb]

Contains a row for each statistics object that exists for the tables, indexes, and indexed views in the database in [!INCLUDE ssNoVersion]. Every index has a corresponding statistics row with the same name and ID (index_id = stats_id), but not every statistics row has a corresponding index.

The catalog view sys.stats_columns provides statistics information for each column in the database.

For more information about statistics, see Statistics.

::: moniker range="=fabric"

Note

For more information on statistics in [!INCLUDE fabric], see Statistics in Fabric Data Warehouse.

::: moniker-end

Column name Data type Description
object_id int ID of the object to which these statistics belong.
name sysname Name of the statistics. Is unique within the object.
stats_id int ID of the statistics. Is unique within the object.

If statistics correspond to an index, the stats_id value is the same as the index_id value in the sys.indexes catalog view.
auto_created bit Indicates whether the statistics were automatically created by [!INCLUDE ssNoVersion].

0 = Statistics weren't automatically created by [!INCLUDE ssNoVersion].

1 = Statistics were automatically created by [!INCLUDE ssNoVersion].
user_created bit Indicates whether the statistics were created by a user.

0 = Statistics weren't created by a user.

1 = Statistics were created by a user.
no_recompute bit Indicates whether the statistics were created with the NORECOMPUTE option.

0 = Statistics weren't created with the NORECOMPUTE option.

1 = Statistics were created with the NORECOMPUTE option.
has_filter bit 0 = Statistics don't have a filter and are computed on all rows.

1 = Statistics have a filter and are computed only on rows that satisfy the filter definition.
filter_definition nvarchar(max) Expression for the subset of rows included in filtered statistics.

NULL = Nonfiltered statistics.
is_temporary bit Indicates whether the statistics is temporary. Temporary statistics support [!INCLUDE ssHADR] secondary databases that are enabled for read-only access.

0 = The statistics isn't temporary.

1 = The statistics is temporary.

Applies to: [!INCLUDE ssNoVersion]
is_incremental bit Indicate whether the statistics are created as incremental statistics.

0 = The statistics aren't incremental.

1 = The statistics are incremental.

Applies to: [!INCLUDE ssSQL14] and later versions.
has_persisted_sample bit Indicates whether the statistics were created or updated with the PERSIST_SAMPLE_PERCENT option.

0 = Statistics aren't persisting the sample percentage.

1 = Statistics were created or updated with the PERSIST_SAMPLE_PERCENT option.

Applies to: [!INCLUDE sssql19-md] and later versions.
stats_generation_method int Indicates the method by which statistics are created.

0 = Sort based statistics

1 = Internal use only

Applies to: [!INCLUDE sssql19-md] and later versions.
stats_generation_method_desc varchar(255) The text description of the method by which statistics are created.

Sort based statistics

Internal use only

Applies to: [!INCLUDE sssql19-md] and later versions.
auto_drop bit Indicates whether or not the auto drop feature is enabled for this statistics object. The AUTO_DROP property allows the creation of statistics objects in a mode such that a subsequent schema change isn't blocked by the statistic object, but instead the statistics are dropped as necessary. In this way, manually created statistics with AUTO_DROP enabled behave like autocreated statistics. For more information, see AUTO_DROP option.

Applies to: [!INCLUDE ssSQL22] and later versions, [!INCLUDE ssazure-sqldb], [!INCLUDE ssazuremi-md].
replica_role_id tinyint Indicates the replica in which auto stats were last updated from.

1 = Primary

2 = Secondary

3 = Geo Secondary

4 = Geo HA Secondary

Applies to: [!INCLUDE sssql25-md] and later versions, [!INCLUDE ssazure-sqldb], [!INCLUDE ssazuremi-md].
replica_role_desc nvarchar(60) Primary, Secondary, Geo Secondary, Geo HA Secondary

Applies to: [!INCLUDE sssql25-md] and later versions, [!INCLUDE ssazure-sqldb], [!INCLUDE ssazuremi-md].
replica_name sysname Instance name of the replica in the availability group. NULL for the primary replica

Applies to: [!INCLUDE sssql25-md] and later versions, [!INCLUDE ssazure-sqldb], [!INCLUDE ssazuremi-md]

Permissions

[!INCLUDE ssCatViewPerm] For more information, see Metadata visibility configuration.

Examples

The following examples return all the statistics and statistics columns for the HumanResources.Employee table.

USE AdventureWorks2022;
GO

SELECT s.name AS statistics_name,
       c.name AS column_name,
       sc.stats_column_id
FROM sys.stats AS s
     INNER JOIN sys.stats_columns AS sc
         ON s.object_id = sc.object_id
        AND s.stats_id = sc.stats_id
     INNER JOIN sys.columns AS c
         ON sc.object_id = c.object_id
        AND c.column_id = sc.column_id
WHERE s.object_id = OBJECT_ID('HumanResources.Employee');

Related content