STATS_DATE (Transact-SQL)

STATS_DATE (Transact-SQL)

 

THIS TOPIC APPLIES TO: yesSQL Server (starting with 2008) yesAzure SQL Database yesAzure SQL Data Warehouse 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.

Topic link icon Transact-SQL Syntax Conventions


STATS_DATE ( object_id , stats_id )
-- Azure SQL Data Warehouse and Parallel Data Warehouse
STATS_DATE (object_id ,stats_id )

object_id

ID of the table or indexed view with the statistics.

stats_id

ID of the statistics object.

Returns datetime on success. Returns NULL on error.

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

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

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

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

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

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

Community Additions

ADD
Show:
© 2016 Microsoft