Sql-server – Script for rebuilding and reindexing the fragmented index

fragmentationindexingsql-server

Can anyone provide the script for rebuilding and re-indexing the fragmented index when 'avg_fragmentation_in_percent' exceeds certain limits (better if cursor is not used)?

Best Solution

To rebuild use:

ALTER INDEX __NAME_OF_INDEX__ ON __NAME_OF_TABLE__ REBUILD

or to reorganize use:

ALTER INDEX __NAME_OF_INDEX__ ON __NAME_OF_TABLE__ REORGANIZE

Reorganizing should be used at lower (<30%) fragmentations but only rebuilding (which is heavier to the database) cuts the fragmentation down to 0%.
For further information see https://msdn.microsoft.com/en-us/library/ms189858.aspx