sys.stats (Transact-SQL)sys.stats (Transact-SQL)

適用於: 是SQL Server 是Azure SQL Database 是Azure SQL 資料倉儲 是平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

針對 SQL ServerSQL Server 資料庫中資料表、索引和索引檢視表的每個統計資料物件,各包含一個資料列。Contains a row for each statistics object that exists for the tables, indexes, and indexed views in the database in SQL ServerSQL Server. 每個索引都有對應的統計資料資料列具有相同的名稱和識別碼 (index_id = stats_id),但並非所有的統計資料資料列都有對應的索引。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.

目錄檢視sys.stats_columns提供資料庫中的每個資料行的統計資料資訊。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_idobject_id intint 這些統計資料所屬物件的識別碼。ID of the object to which these statistics belong.
namename sysnamesysname 統計資料的名稱。Name of the statistics. 在物件中,這是唯一的。Is unique within the object.
stats_idstats_id intint 統計資料的識別碼。ID of the statistics. 在物件中,這是唯一的。Is unique within the object.

如果統計資料對應到索引, stats_id值是相同index_id中的值sys.indexes目錄檢視。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_createdauto_created bitbit 指出 SQL ServerSQL Server 是否自動建立統計資料。Indicates whether the statistics were automatically created by SQL ServerSQL Server.

0 = SQL ServerSQL Server 未自動建立統計資料。0 = Statistics were not automatically created by SQL ServerSQL Server.

1 = SQL ServerSQL Server 自動建立統計資料。1 = Statistics were automatically created by SQL ServerSQL Server.
user_createduser_created bitbit 指出使用者是否建立統計資料。Indicates whether the statistics were created by a user.

0 = 使用者未建立統計資料。0 = Statistics were not created by a user.

1 = 使用者建立統計資料。1 = Statistics were created by a user.
no_recomputeno_recompute bitbit 指出是否使用已建立統計資料NORECOMPUTE選項。Indicates whether the statistics were created with the NORECOMPUTE option.

0 = 統計資料不建立與NORECOMPUTE選項。0 = Statistics were not created with the NORECOMPUTE option.

1 = 使用建立統計資料NORECOMPUTE選項。1 = Statistics were created with the NORECOMPUTE option.
has_filterhas_filter bitbit 0 = 統計資料沒有篩選,而且會在所有資料列上計算。0 = Statistics do not have a filter and are computed on all rows.

1 = 統計資料有篩選,而且只會在滿足篩選定義的資料列上計算。1 = Statistics have a filter and are computed only on rows that satisfy the filter definition.
filter_definitionfilter_definition nvarchar(max)nvarchar(max) 包含在已篩選之統計資料內的資料列子集運算式。Expression for the subset of rows included in filtered statistics.

NULL = 非篩選的統計資料。NULL = Non-filtered statistics.
is_temporaryis_temporary bitbit 適用於SQL Server 2012 (11.x)SQL Server 2012 (11.x)SQL Server 2017SQL Server 2017Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017.

指出統計資料是否為暫時性。Indicate whether the statistics is temporary. 暫時性統計資料支援已啟用唯讀存取的 AlwaysOn 可用性群組Always On availability groups 次要資料庫。Temporary statistics support AlwaysOn 可用性群組Always On availability groups secondary databases that are enabled for read-only access.

0 = 統計資料不是暫時性。0 = The statistics is not temporary.

1 = 統計資料是暫時性。1 = The statistics is temporary.
is_incrementalis_incremental bitbit 適用於SQL Server 2014 (12.x)SQL Server 2014 (12.x)SQL Server 2017SQL Server 2017Applies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) through SQL Server 2017SQL Server 2017.

指出是否將統計資料建立成累加統計資料。Indicate whether the statistics are created as incremental statistics.

0 = 統計資料不是累加的。0 = The statistics are not incremental.

1 = 統計資料是累加的。1 = The statistics are incremental.

PermissionsPermissions

目錄檢視內中繼資料的可見性會限制在使用者所擁有的安全性實體,或已授與使用者某些權限的安全性實體。The visibility of the metadata in catalog views is limited to securables that a user either owns or on which the user has been granted some permission. 如需相關資訊,請參閱 Metadata Visibility ConfigurationFor more information, see Metadata Visibility Configuration.

範例Examples

下列範例會傳回 HumanResources.Employee 資料表的所有統計資料及統計資料行。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

物件目錄檢視 (Transact-SQL) Object Catalog Views (Transact-SQL)
目錄檢視 (Transact-SQL) Catalog Views (Transact-SQL)
查詢 SQL Server 系統目錄常見問題集 Querying the SQL Server System Catalog FAQ
統計資料 Statistics
sys.dm_db_stats_properties (Transact-SQL) sys.dm_db_stats_properties (Transact-SQL)
sys.dm_db_stats_histogram (-SQL)) sys.dm_db_stats_histogram (Transact-SQL)
sys.stats_columns (Transact-SQL)sys.stats_columns (Transact-SQL)