Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.
I ran across the STATS_DATE function recently, and it’s one that I hadn’t used in production code. I’m not sure how this escaped me, as it was added in SQL Server 2008, but I rarely see it written about, so it’s not just me.
This function takes an object_id and a stats_id, and returns the date the statistics were last updated. The statistics id is the id from sys.stats and doesn’t necessarily correspond to the index ID.
As a quick example, if you look at the Sales.SalesOrderHeader table in AdventureWorks2012, you can run this:
SELECT STATS_DATE ( 1266103551 , 2)
This should return a simple date. I don’t know if you’ll have the same date in your database, but I assume this is the default date for the sample database.
Obviously these stats are out of date.
Or are they? I don’t use this database a lot and haven’t changed the data in this table that I’m aware of. In that case, they may be up to date.
This can be a handy function, but remember, the age of stats only matters if you’ve had data changes. However with having an understanding of both pieces of information, you might use this to accelerate statistics rebuilds ahead of what AUTO STATISTICS might do.
This was a good chance to dig into and look at how a function works in SQL and how I might use it. You could write this easily.