Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Update statistics?

Author  Topic 

NguyenL71
Posting Yak Master

228 Posts

Posted - 2012-03-27 : 12:08:39
Hi,

If you have to choose which option do you implement, which one would you pick and why?.  Please provide the main reasons.  SQL 2008.

Thanks in advance.

--#1
ALTER INDEX ON Table1 REBUILD;
UPDATE STATISTICS Table1 WITH ALL;

--FULLSCAN: option is used to specify all the rows in a table should be retrived to generate the key distribution statistics.

--#2
ALTER INDEX ON Table1 REBUILD;
UPDATE STATISTICS Table1 WITH FULLSCAN;


-------------------------------------------------------------------

-- Return when statistics were last updated?.
SELECT CAST(OBJECT_NAME(object_id) AS VARCHAR(40)) AS 'TableName'
,CAST(name AS VARCHAR(40)) AS 'IndexName'
,STATS_DATE(object_id, stats_id) AS 'LastUpdateStatistics'
FROM sys.stats
WHERE ( object_id > 100 ) --Exclude systems.
AND (name NOT LIKE '_WA%')
ORDER BY 'LastUpdateStatistics' DESC, 'TableName' ASC;
GO

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-03-27 : 13:01:32
Neither. Why would I rebuild indexes (which updates stats) and then update the stats again?

p.s. your first option is completely equivalent to this:

ALTER INDEX ON Table1 REBUILD;
UPDATE STATISTICS Table1;

All is the default.

--
Gail Shaw
SQL Server MVP
Go to Top of Page
   

- Advertisement -