Jul 15, 2016

ReIndexing Database Tables in SQL Server

If we create index on table and indexed table frequently changes occur than fragmentation increases. This may reduce your query performance. So we need to rebuild the index time to time.

We can use DBCC DBREINDEX to rebuild the all index on all tables in database. DBCC DBREINDEX drops index and creates again.

Rebuild all indexes in database with specific fill factor


USE [BD_NAME]
GO
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
GO
EXEC sp_updatestats
GO


Rebuild all indexes in database with specific fill factor


USE [BD_NAME]
GO
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?')"
GO
EXEC sp_updatestats
GO


sp_updatestats insures that “Statistics for all tables have been updated.”

Here question mark is a placeholder for the table name (as it loops through each table in turn).

To rebuild all index in a table


USE [DB_NAME]
GO
ALTER INDEX ALL ON TBL_NAME
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
              STATISTICS_NORECOMPUTE = ON);
GO