| title | sys.stats (Transact-SQL) | ||||
|---|---|---|---|---|---|
| description | sys.stats (Transact-SQL) | ||||
| author | rwestMSFT | ||||
| ms.author | randolphwest | ||||
| ms.date | 10/13/2022 | ||||
| ms.service | sql | ||||
| ms.subservice | system-objects | ||||
| ms.topic | reference | ||||
| f1_keywords |
|
||||
| helpviewer_keywords |
|
||||
| dev_langs |
|
||||
| monikerRange | >=aps-pdw-2016||=azuresqldb-current||=azure-sqldw-latest||>=sql-server-2016||>=sql-server-linux-2017||=azuresqldb-mi-current |
[!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]) |
| 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 will not be blocked by the statistic object, but instead the statistics will be dropped as necessary. In this way, manually created statistics with AUTO_DROP enabled behave like auto-created statistics. For more information, see AUTO_DROP option. Applies to: [!INCLUDEssSDSfull], [!INCLUDEssazuremi_md], and starting with [!INCLUDEssSQL22]. |
[!INCLUDEssCatViewPerm] For more information, see Metadata Visibility Configuration.
The following examples return all the statistics and statistics columns for the HumanResources.Employee table.
USE AdventureWorks2019;
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');