Tuesday, March 05, 2013

Rebuilding all indexes on a table and specifying options

If you are planning rebuild index  to use this statement below , you must run update statistics statement after rebuild index. Statistics are always updated when you rebuild index.  But STATISTICS_NORECOMPUTE=ON disable the auto update statistics from updating the specific statistics for an index (or column-level statistics)

-- Try to avoid this options
USE AdventureWorks2012;
GO
ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
              STATISTICS_NORECOMPUTE = ON);
GO


UPDATE STATISTICS (Production.Product)
GO

(OR)

Alternatively you can use below options

-- By default STATISTICS_NORECOMPUTE = OFF

USE AdventureWorks2012;
GO
ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
              STATISTICS_NORECOMPUTE = OFF);


--  For single index options
ALTER INDEX[IDX_INDEX_NAME] ON [dbo].[Product]   REBUILD WITH (STATISTICS_NORECOMPUTE=OFF)


Source: Microsoft needs to correct this page.
http://technet.microsoft.com/en-us/library/ms188388.aspx