Truncate Table and UPDATE Statistics

Question

Do we need to Update table statistics after calling Truncate table or it gets updated automatically?

Q: Do we need to call "UPDATE STATISTICS" after truncating a table?

Answer 1

It depends on how your administrator has configured statistics. The usual approach would be a weekly maintenance job. Then you could either wait for the job to run, or update the statistics manually.

There's also the option to automatically update statistics:

When the automatic update statistics option, AUTO_UPDATE_STATISTICS, is on, the query optimizer determines when statistics might be out-of-date and then updates them when they are used by a query.

That would probably recalculate after a truncate.

You can turn on auto-updates like:

ALTER DATABASE AdventureWorks
    SET AUTO_UPDATE_STATISTICS ON;

And to update statistics manually:

UPDATE STATISTICS Sales.SalesOrderDetail

To verify the current age of your statistics, run:

SELECT 
    object_name = Object_Name(ind.object_id),
    IndexName = ind.name,
    StatisticsDate = STATS_DATE(ind.object_id, ind.index_id)
FROM SYS.INDEXES ind
order by STATS_DATE(ind.object_id, ind.index_id) desc

Answer 2

As you have no data it will be meaningless until the data gets inserted and then you would look to update the stats.

Dont forget you can auto update stats as well as running and update statistics job daily/weekly etc...

If there is still a major concern about it,j ust truncate and then update the stats on the table.

Answer 3

Stats are not automatically updated until the stats are needed again. aka, the TRUNCATE does not do it. So "No".

The original answer was "Yes" because it's not automatic as part of TRUNCATE. It depends how you read the question :-)

Remember, statistics are updated automatically when needed by a query (eg number of row change). From "Index Statistics" in BOL

A statistics update is initiated whenever the statistics used in a query execution plan fail a test for current statistics

One way to verify using STATS_DATE...

SELECT
   name AS index_name, 
   STATS_DATE(object_id, index_id)
FROM
   sys.indexes 
WHERE
   object_id = OBJECT_ID('MyTruncatedTable')

Edit: I wanted to make I sure :-)

You'll see stats are only updated by the SELECT statements, not the INSERT, DELETE or TRUNCATE

IF OBJECT_ID('dbo.foo') IS NOT NULL DROP TABLE dbo.foo
CREATE TABLE dbo.foo (
    bar int NOT NULL IDENTITY (1, 1) PRIMARY KEY,
    thing int NOT NULL
)
CREATE INDEX IX_thing ON dbo.foo (thing)

INSERT dbo.foo (thing) SELECT c1.object_id FROM sys.columns c1, sys.columns c2
SELECT
   name AS index_name, 
   STATS_DATE(object_id, index_id) AS AfterLoad
FROM sys.indexes WHERE object_id = OBJECT_ID('dbo.foo')

SELECT DISTINCT thing FROM dbo.foo ORDER BY thing DESC
SELECT
   name AS index_name, 
   STATS_DATE(object_id, index_id) AS AfterFirstQuery
FROM sys.indexes WHERE object_id = OBJECT_ID('dbo.foo')

DELETE TOP (50000) dbo.foo
SELECT
   name AS index_name, 
   STATS_DATE(object_id, index_id) AS AfterDelete
FROM sys.indexes WHERE object_id = OBJECT_ID('dbo.foo')

SELECT DISTINCT thing FROM dbo.foo ORDER BY thing DESC
SELECT
   name AS index_name, 
   STATS_DATE(object_id, index_id) AS After2ndQuery
FROM sys.indexes WHERE object_id = OBJECT_ID('dbo.foo')

TRUNCATE TABLE dbo.foo
SELECT
   name AS index_name, 
   STATS_DATE(object_id, index_id) AS AfterTruncate
FROM sys.indexes WHERE object_id = OBJECT_ID('dbo.foo')

SELECT DISTINCT thing FROM dbo.foo ORDER BY thing DESC
SELECT
   name AS index_name, 
   STATS_DATE(object_id, index_id) AS After3rdQuery
FROM sys.indexes WHERE object_id = OBJECT_ID('dbo.foo')