This topic has not yet been rated - Rate this topic

STATS_DATE (Transact-SQL)

SQL Server 2012

Returns the date of the most recent update for statistics on a table or indexed view.

For more information about updating statistics, see Statistics.

Topic link icon Transact-SQL Syntax Conventions

STATS_DATE ( object_id , stats_id )

Arguments

object_id: ID of the table or indexed view with the statistics.
stats_id: ID of the statistics object.

Return Types

Returns datetime on success. Returns NULL on error.

Remarks

System functions can be used in the select list, in the WHERE clause, and anywhere an expression can be used.

Permissions

Requires membership in the db_owner fixed database role or permission to view the metadata for the table or indexed view.

Examples

A. Return the dates of the most recent statistics for a table

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

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. 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

Reference

System Functions (Transact-SQL)

UPDATE STATISTICS (Transact-SQL)

sp_autostats (Transact-SQL)

Concepts

Statistics

Did you find this helpful? Yes No

Not accurate

Not enough depth

Need more code examples

(1500 characters remaining)

Community Additions

ADD