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



No comments:

Post a Comment

Please do not enter any spam link in the comment box.

Related Posts

What is the Use of isNaN Function in JavaScript? A Comprehensive Explanation for Effective Input Validation

In the world of JavaScript, input validation is a critical aspect of ensuring that user-provided data is processed correctly. One indispensa...