| title | description | author | ms.author | ms.date | ms.service | ms.subservice | ms.topic | f1_keywords | helpviewer_keywords | dev_langs | monikerRange | ||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
sys.stats (Transact-SQL) |
sys.stats (Transact-SQL) |
rwestMSFT |
randolphwest |
10/13/2022 |
sql |
system-objects |
reference |
|
|
|
>=aps-pdw-2016||=azuresqldb-current||=azure-sqldw-latest||>=sql-server-2016||>=sql-server-linux-2017||=azuresqldb-mi-current||=fabric |
[!INCLUDE sql-asdb-asdbmi-asa-pdw-fabricse-fabricdw]
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.
::: moniker range="=fabric"
Note
For more information on statistics in [!INCLUDE fabric], see [Statistics in [!INCLUDE fabric]](/fabric/data-warehouse/statistics).
::: 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 [!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: [!INCLUDE ssazure-sqldb], [!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 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');