TSQL- Rebuild all indexes on a table

As we know that Indexes are the most powerful feature of SQL because they provide opportunities for much more efficient use of I/O and they have great potential. Indexes are very useful for many applications but come with some limitations. Another key factor is that SQL Server Database Engine is responsible to automatically modifies indexes whenever insert, update, or delete operations are made to the underlying data.

Why do we need to rebuild all indexes on a table?

Suppose that you are working on a SQL data table which is containing high volume of data with clustered or non-clustered indexes then it must be possible that the information in the index to become scattered in the database and this term is known as Fragmentation.

Fragmentation exists when indexes have pages in which the logical ordering, based on the key value, does not match the physical ordering inside the data file. If your database is carrying the heavily fragmented indexes then it can degrade query performance and cause your business application to respond slowly, specifically scan operations.

Permissions – Before using of DBREINDEX command, user must own the table, or be a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role.

DBREINDEX –SQL Server provides a single T-SQL command DBCC DBREINDEX which can rebuild all the indexes for a table in one statement. By allowing an index to be rebuilt dynamically, indexes enforcing either PRIMARY KEY or UNIQUE constraints can be rebuilt without having to drop and re-create those constraints. 

DBCC DBREINDEX  ( table_name [ , index_name [ , fillfactor ] ] )      [ WITH NO_INFOMSGS ] Arguments table_name is the name of the table containing the specified index or indexes to rebuild. ndex_name is the name of the index to rebuild. If index_name is specified, table_name must be specified. If index_name is not specified or is " ", all indexes for the table are rebuilt. fillfactor is the percentage of space on each index page for storing data when the index is created or rebuilt. WITH NO_INFOMSGS - Suppresses all informational messages that have severity levels from 0 through 10.

To avoid multiple coding lines for DROP INDEX and CREATE INDEX statements, DBCC DBREINDEX is the best way because -

1. It can rebuild all the indexes for a table in one statement.
2. It is automatically atomic, whereas individual DROP INDEX and CREATE INDEX statements must be included in a transaction to be atomic. 
3. It is an offline operation. If a nonclustered index is being rebuilt, a shared lock is held on the table in question for the duration of the operation.

Restrictions –If there are some benefits of this command then it contains some restrictions also where it is not supported for use on the following objects:

System tables, Spatial indexes and xVelocity memory optimized columnstore indexes

To understand this command, we can take an example as given below where sales order table is containing two indexes such as clustered index and non-clustered index -

Rebuilding an index – If we want to rebuild an index on a table only then we can use the following command as given below-

USE Demo;   GO  ----- table_name : dbo.TBL_SALES_ORDER ----- index_name : ClusteredIndex-20180205-110848 ----- fillfactor : 80 DBCC DBREINDEX ('dbo.TBL_SALES_ORDER', 'ClusteredIndex-20180205-110848',80);  GO

Rebuilding all indexes - If we want to rebuild all indexes on a table then we can use the following command as given below -

USE Demo;   GO  ----- table_name : dbo.TBL_SALES_ORDER ----- index_name : ‘’ ----- fillfactor : 80 DBCC DBREINDEX ('dbo.TBL_SALES_ORDER', '',70);  GO

Rebuilding all indexes on all tables - If we want to rebuild all indexes on all the tables then we can use the following command as given below -

---- declare local variables DECLARE @tblName VARCHAR(255) DECLARE @Query NVARCHAR(500) ---- declare and set fillfactor DECLARE @fillfactor INT= 80 ---- declare cursor table DECLARE TableCursor CURSOR FOR SELECT OBJECT_SCHEMA_NAME([object_id])+'.'+name AS TableName FROM sys.tables ---- Open cursor OPEN TableCursor ---- Fetch first value from the cursor FETCH NEXT FROM TableCursor INTO @tblName ---- conditional while loop WHILE @@FETCH_STATUS = 0 BEGIN ---- set query statement for table with all indexes SET @Query = 'DBCC DBREINDEX ('''+@tblName+''','','+@fillfactor+')' ---- Execute Query statement EXEC (@Query) ---- Fetch next value from the cursor FETCH NEXT FROM TableCursor INTO @tblName END ---- close cursor variabe CLOSE TableCursor ---- deallocate cursor variabe DEALLOCATE TableCursor GO

Conclusion

Unlike DBCC INDEXDEFRAG, or ALTER INDEX with the REORGANIZE option, DBCC DBREINDEX is an offline operation. If a nonclustered index is being rebuilt, a shared lock is held on the table in question for the duration of the operation. This prevents modifications to the table.  By using this command, we can rebuild all indexes on all the tables with some minimal T-SQL codes.