Truncation Table and UPDATE Statistics

Question

Do I need to update table statistics after calling the Truncate table or is it automatically updated?

Q: Do we need to call “UPDATE STATISTICS” after trimming the table?

Answer 1

Statistics are not updated automatically until statistics are needed again. aka, TRUNCATE does not. So, no.

The initial answer was “Yes,” because it is not automatic as part of TRUNCATE. It depends on how you read the question :-)

, (, ). " " BOL

, , ,

STATS_DATE...

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

: , : -)

, SELECT, INSERT, DELETE 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')

Answer 2

, . - . , .

:

AUTO_UPDATE_STATISTICS, , , , .

, , .

, :

ALTER DATABASE AdventureWorks
    SET AUTO_UPDATE_STATISTICS ON;

:

UPDATE STATISTICS Sales.SalesOrderDetail

, :

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 3

Since you do not have data , it will be pointless until the data is inserted, and then you want to update the statistics.

Do not forget that you can automatically update statistics, as well as run and update statistics daily / weekly, etc.

If this is still a serious concern, j ust truncates and then updates the statistics on the table.