sys-stats-transact-sql.md

title	sys.stats (Transact-SQL)
description	sys.stats (Transact-SQL)
author	rwestMSFT
ms.author	randolphwest
ms.date	12/18/2017
ms.prod	sql
ms.prod_service	database-engine, sql-database, synapse-analytics, pdw
ms.technology	system-objects
ms.topic	reference
f1_keywords	sys.stats stats_TSQL sys.stats_TSQL stats
helpviewer_keywords	sys.stats catalog view
dev_langs	TSQL
ms.assetid	42605c80-126f-460a-befb-a0b7482fae6a
monikerRange	>=aps-pdw-2016||=azuresqldb-current||=azure-sqldw-latest||>=sql-server-2016||>=sql-server-linux-2017||=azuresqldb-mi-current

sys.stats (Transact-SQL)

[!INCLUDE sql-asdb-asdbmi-asa-pdw]

Contains a row for each statistics object that exists for the tables, indexes, and indexed views in the database in [!INCLUDEssNoVersion]. Every index will have 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.

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 [!INCLUDEssNoVersion]. 0 = Statistics were not automatically created by [!INCLUDEssNoVersion]. 1 = Statistics were automatically created by [!INCLUDEssNoVersion].
user_created	bit	Indicates whether the statistics were created by a user. 0 = Statistics were not 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 were not created with the NORECOMPUTE option. 1 = Statistics were created with the NORECOMPUTE option.
has_filter	bit	0 = Statistics do not 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 = Non-filtered statistics.
is_temporary	bit	Indicates whether the statistics is temporary. Temporary statistics support [!INCLUDEssHADR] secondary databases that are enabled for read-only access. 0 = The statistics is not temporary. 1 = The statistics is temporary. Applies to: [!INCLUDEssNoVersion] (Starting with [!INCLUDEssSQL11])
is_incremental	bit	Indicate whether the statistics are created as incremental statistics. 0 = The statistics are not incremental. 1 = The statistics are incremental. Applies to: [!INCLUDEssNoVersion] (Starting with [!INCLUDEssSQL14])
has_persisted_sample	bit	Indicates whether the statistics were created or updated with the PERSIST_SAMPLE_PERCENT option. 0 = Statistics are not persisting the sample percentage. 1 = Statistics were created or updated with the PERSIST_SAMPLE_PERCENT option. Applies to: [!INCLUDEssNoVersion] (Starting with [!INCLUDEsssql19-md])
stats_generation_method	int	Indicates the method by which statistics are created. 0 = Sort based statistics 1 = Internal use only Applies to: [!INCLUDEssNoVersion] (Starting with [!INCLUDEsssql19-md])
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: [!INCLUDEssNoVersion] (Starting with [!INCLUDEsssql19-md])

Permissions

[!INCLUDEssCatViewPerm] For more information, see Metadata Visibility Configuration.

Examples

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

USE AdventureWorks2012;  
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');  

See Also

Object Catalog Views (Transact-SQL)
Catalog Views (Transact-SQL)
Querying the SQL Server System Catalog FAQ
Statistics
sys.dm_db_stats_properties (Transact-SQL)
sys.dm_db_stats_histogram (Transact-SQL)
sys.stats_columns (Transact-SQL)