sql - 截断表和UPDATE统计信息

调用截断表后我们是否需要更新表统计信息，或者它会自动更新？

问:截断一个表后，我们需要调用“UPDATE STATISTICS”吗？

最佳答案

统计信息不会自动更新，直到再次需要统计信息为止。 aka，TRUNCATE不会这样做。
所以不行”。

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

请记住，查询需要时，统计信息会自动更新(例如，行数更改)。来自BOL中的“Index Statistics”



一种使用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')

关于sql - 截断表和UPDATE统计信息，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/1730232/