STATS_DATE (Transact-SQL)STATS_DATE (Transact-SQL)

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

針對資料表或索引檢視表的統計資料傳回最近更新的日期。Returns the date of the most recent update for statistics on a table or indexed view.

如需有關更新統計資料的詳細資訊,請參閱 統計資料For more information about updating statistics, see Statistics.

主題連結圖示 Transact-SQL 語法慣例Topic link icon Transact-SQL Syntax Conventions

語法Syntax

STATS_DATE ( object_id , stats_id )  

引數Arguments

object_idobject_id
包含統計資料之資料表或索引檢視表的識別碼。ID of the table or indexed view with the statistics.

stats_idstats_id
統計資料物件的識別碼。ID of the statistics object.

傳回類型Return Types

成功時傳回 datetimeReturns datetime on success. 若未建立統計資料 Blob,則傳回NULLReturns NULL if a statistics blob was not created.

RemarksRemarks

系統函數可以用於選取清單、WHERE 子句以及任何可以使用運算式的位置。System functions can be used in the select list, in the WHERE clause, and anywhere an expression can be used.

統計資料更新將日期儲存在統計資料 Blob 物件中,其中還有長條圖密度向量,不是儲存在中繼資料中。Statistics update date is stored in the statistics blob object together with the histogram and density vector, not in the metadata. 如果沒有讀取資料以產生統計資料,則不會建立統計 Blob,而且日期也不可使用。When no data is read to generate statistics data, the statistics blob is not created, and the date is not available. 這是篩選過的統計資料述詞未傳回任何資料列,或新的空白資料表的情況。This is the case for filtered statistics for which the predicate does not return any rows, or for new empty tables.

如果統計資料對應到索引,sys.stats 目錄檢視中的 stats_id 值會與 sys.indexes 目錄檢視中的 index_id 值相同。If statistics correspond to an index, the stats_id value in the sys.stats catalog view is the same as the index_id value in the sys.indexes catalog view.

權限Permissions

需要 db_owner 固定資料庫角色中的成員資格或權限,才能檢視資料表或索引檢視表的中繼資料。Requires membership in the db_owner fixed database role or permission to view the metadata for the table or indexed view.

範例Examples

A.A. 針對資料表傳回最近更新統計資料的日期Return the dates of the most recent statistics for a table

下列範例會針對 Person.Address 資料表的每個統計資料物件傳回最近更新的日期。The following example returns the date of the most recent update for each statistics object on the Person.Address table.

USE AdventureWorks2012;  
GO  
SELECT name AS stats_name,   
    STATS_DATE(object_id, stats_id) AS statistics_update_date  
FROM sys.stats   
WHERE object_id = OBJECT_ID('Person.Address');  
GO  

如果統計資料對應到索引,sys.stats 目錄檢視中的 stats_id 值會與 sys.indexes 目錄檢視中的 index_id 值相同,而且下列查詢會傳回與之前查詢相同的結果。If statistics correspond to an index, the stats_id value in the sys.stats catalog view is the same as the index_id value in the sys.indexes catalog view, and the following query returns the same results as the preceding query. 如果統計資料未對應到索引,統計資料會在 sys.stats 結果中而不是 sys.indexes 結果中。If statistics do not correspond to an index, they are in the sys.stats results but not in the sys.indexes results.

USE AdventureWorks2012;  
GO  
SELECT name AS index_name,   
    STATS_DATE(object_id, index_id) AS statistics_update_date  
FROM sys.indexes   
WHERE object_id = OBJECT_ID('Person.Address');  
GO  

範例:Azure Synapse Analytics (SQL DW)Azure Synapse Analytics (SQL DW)平行處理資料倉儲Parallel Data WarehouseExamples: Azure Synapse Analytics (SQL DW)Azure Synapse Analytics (SQL DW) and 平行處理資料倉儲Parallel Data Warehouse

B.B. 了解具名統計資料上次更新時間Learn when a named statistics was last updated

下列範例會建立 DimCustomer 資料表的 LastName 資料行的統計資料。The following example creates statistics on the LastName column of the DimCustomer table. 然後,它會執行查詢,以顯示統計資料的日期。It then runs a query to show the date of the statistics. 接著它會更新統計資料,並再次執行查詢,以顯示更新的日期。Then it udpates the statistics and runs the query again to show the updated date.

--First, create a statistics object  
USE AdventureWorksPDW2012;  
GO  
CREATE STATISTICS Customer_LastName_Stats  
ON AdventureWorksPDW2012.dbo.DimCustomer (LastName)  
WITH SAMPLE 50 PERCENT;  
GO  
  
--Return the date when Customer_LastName_Stats was last updated  
USE AdventureWorksPDW2012;  
GO  
SELECT stats_id, name AS stats_name,   
    STATS_DATE(object_id, stats_id) AS statistics_date  
FROM sys.stats s  
WHERE s.object_id = OBJECT_ID('dbo.DimCustomer')  
    AND s.name = 'Customer_LastName_Stats';  
GO  
  
--Update Customer_LastName_Stats so it will have a different timestamp in the next query  
GO  
UPDATE STATISTICS dbo.dimCustomer (Customer_LastName_Stats);  
  
--Return the date when Customer_LastName_Stats was last updated.  
SELECT stats_id, name AS stats_name,   
    STATS_DATE(object_id, stats_id) AS statistics_date  
FROM sys.stats s  
WHERE s.object_id = OBJECT_ID('dbo.DimCustomer')  
    AND s.name = 'Customer_LastName_Stats';  
GO    

C.C. 檢視資料表上所有統計資料的上次更新日期View the date of the last update for all statistics on a table

這個範例會傳回 DimCustomer 資料表上每個統計資料物件上次更新的日期。This example returns the date for when each statistics object on the DimCustomer table was last updated.

--Return the dates all statistics on the table were last updated.  
SELECT stats_id, name AS stats_name,   
    STATS_DATE(object_id, stats_id) AS statistics_date  
FROM sys.stats s  
WHERE s.object_id = OBJECT_ID('dbo.DimCustomer');  
GO  

如果統計資料對應到索引,sys.stats 目錄檢視中的 stats_id 值會與 sys.indexes 目錄檢視中的 index_id 值相同,而且下列查詢會傳回與之前查詢相同的結果。If statistics correspond to an index, the stats_id value in the sys.stats catalog view is the same as the index_id value in the sys.indexes catalog view, and the following query returns the same results as the preceding query. 如果統計資料未對應到索引,統計資料會在 sys.stats 結果中而不是 sys.indexes 結果中。If statistics do not correspond to an index, they are in the sys.stats results but not in the sys.indexes results.

USE AdventureWorksPDW2012;  
GO  
SELECT name AS index_name,   
    STATS_DATE(object_id, index_id) AS statistics_update_date  
FROM sys.indexes   
WHERE object_id = OBJECT_ID('dbo.DimCustomer');  
GO  

另請參閱See Also

系統函數 (Transact-SQL) System Functions (Transact-SQL)
UPDATE STATISTICS (Transact-SQL) UPDATE STATISTICS (Transact-SQL)
sp_autostats (Transact-SQL) sp_autostats (Transact-SQL)
統計資料 Statistics
sys.dm_db_stats_properties (Transact-SQL) sys.dm_db_stats_properties (Transact-SQL)
sys.statssys.stats