If I run Rebuild Index Task in maintenance plan, do I have to update statistics using Update Statistcs Task?
2 Answers
Yes
Column statistics are not rebuilt, only index statistics
--random data
CREATE TABLE dbo.foo (bar int PRIMARY KEY, humbug char(2000))
INSERT dbo.foo (bar, humbug) VALUES (1, 'aaa')
INSERT dbo.foo (bar, humbug) VALUES (2, 'bbb')
INSERT dbo.foo (bar, humbug) VALUES (3, 'ccc')
INSERT dbo.foo (bar, humbug) VALUES (4, 'aaa')
INSERT dbo.foo (bar, humbug) VALUES (5, 'bbb')
INSERT dbo.foo (bar, humbug) VALUES (6, 'ccc')
INSERT dbo.foo (bar, humbug) VALUES (7, 'ccc')
INSERT dbo.foo (bar, humbug) VALUES (8, 'ccc')
INSERT dbo.foo (bar, humbug) VALUES (9, 'ccc')
INSERT dbo.foo (bar, humbug) VALUES (10, 'cc')
INSERT dbo.foo SELECT bar+10, humbug FROM dbo.foo
--generate index stats + column stats
SELECT * FROM foo WHERE bar = 12
SELECT * FROM foo WHERE humbug = 'bbb'
--created date
SELECT
OBJECT_SCHEMA_NAME([id]) + '.' + OBJECT_NAME([id]),
NAME,
STATS_DATE ([id], indid)
FROM
dbo.sysindexes
WHERE
OBJECT_NAME([id]) = 'foo'
ORDER BY
STATS_DATE ([id], indid) DESC
--Relax, have a coffee
--rebuild
ALTER INDEX ALL ON dbo.foo REBUILD WITH (FILLFACTOR = 90)
--check again
SELECT
OBJECT_SCHEMA_NAME([id]) + '.' + OBJECT_NAME([id]),
NAME,
STATS_DATE ([id], indid)
FROM
dbo.sysindexes
WHERE
OBJECT_NAME([id]) = 'foo'
ORDER BY
STATS_DATE ([id], indid) DESC
--only index stats were updated
No. Rebuilding the indexes will generate new statistics "for free".
-
-
2Rebuilding an index, for example by using ALTER INDEX … REBUILD, will also update index statistics with the equivalent of using WITH FULLSCAN. Rebuilding indexes does not update column statistic (see sqlblog.com/blogs/ben_nevarez/archive/2009/10/06/… for details)– SergeyJan 28, 2011 at 14:26