sys-stats-transact-sql.md

title	sys.stats (Transact-SQL) | Microsoft Docs
ms.custom
ms.date	12/18/2017
ms.prod	sql-non-specified
ms.prod_service	database-engine, sql-database, sql-data-warehouse, pdw
ms.service
ms.component	system-catalog-views
ms.reviewer
ms.suite	sql
ms.technology	database-engine
ms.tgt_pltfrm
ms.topic	language-reference
f1_keywords	sys.stats stats_TSQL sys.stats_TSQL stats
dev_langs	TSQL
helpviewer_keywords	sys.stats catalog view
ms.assetid	42605c80-126f-460a-befb-a0b7482fae6a
caps.latest.revision	41
author	edmacauley
ms.author	edmaca
manager	craigg
ms.workload	On Demand

sys.stats (Transact-SQL)

[!INCLUDEtsql-appliesto-ss2008-all-md]

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	Applies to: [!INCLUDEssSQL11] through [!INCLUDEssCurrent]. Indicate 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.
is_incremental	bit	Applies to: [!INCLUDEssSQL14] through [!INCLUDEssCurrent]. Indicate whether the statistics are created as incremental statistics. 0 = The statistics are not incremental. 1 = The statistics are incremental.

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)