Edit

Share via

sys.stats (Transact-SQL)

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) SQL analytics endpoint in Microsoft Fabric Warehouse in Microsoft Fabric

Contains a row for each statistics object that exists for the tables, indexes, and indexed views in the database in SQL Server. 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.

Note

For more information on statistics in Microsoft Fabric, see Statistics in Fabric Data Warehouse.

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 SQL Server.

0 = Statistics weren't automatically created by SQL Server.

1 = Statistics were automatically created by SQL Server.
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 Always On availability groups secondary databases that are enabled for read-only access.

0 = The statistics isn't temporary.

1 = The statistics is temporary.

Applies to: SQL Server
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: SQL Server 2014 (12.x) 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: SQL Server 2019 (15.x) 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: SQL Server 2019 (15.x) 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: SQL Server 2019 (15.x) 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: SQL Server 2022 (16.x) and later versions, Azure SQL Database, Azure SQL Managed Instance.
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: SQL Server 2025 (17.x) Preview and later versions, Azure SQL Database, Azure SQL Managed Instance.
replica_role_desc nvarchar(60) Primary, Secondary, Geo Secondary, Geo HA Secondary

Applies to: SQL Server 2025 (17.x) Preview and later versions, Azure SQL Database, Azure SQL Managed Instance.
replica_name sysname Instance name of the replica in the availability group. NULL for the primary replica

Applies to: SQL Server 2025 (17.x) Preview and later versions, Azure SQL Database, Azure SQL Managed Instance

Permissions

The visibility of the metadata in catalog views is limited to securables that a user either owns, or on which the user was granted some permission. 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