截断表和更新统计信息 [英] Truncate Table and UPDATE Statistics

问题描述

调用Truncate table后需要更新表统计信息还是自动更新?

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

问:截断表后是否需要调用UPDATE STATISTICS"?

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

推荐答案

统计信息不会自动更新，直到再次需要统计信息.也就是 TRUNCATE 不这样做.所以不".

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

最初的答案是是"，因为它不是 TRUNCATE 的一部分自动执行的.这取决于你如何阅读问题:-)

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

请记住，统计信息会在查询需要时自动更新(例如行更改的数量).来自 BOL 中的索引统计"

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

使用 STATS_DATE 进行验证的一种方法...

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

编辑:我想确定:-)

您会看到统计信息仅由 SELECT 语句更新，而不是 INSERT、DELETE 或 TRUNCATE

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

这篇关于截断表和更新统计信息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！