Rebuild index in SQL server

Rebuilding indexes completely drops and re-creates the database indexes. Technically, rebuilding consists of copying, renaming, and dropping the old one, internally. This is one of the operations we will perform to defragment our indexes since we are rebuilding them and realigning the physical and logical order of the index row in contiguous pages from scratch. Fragmentation is eliminated, pages are compacted together, and disk space is reclaimed.

Syntax

ALTER INDEX [IndexName] ON [SchemaName].[TableName] REBUILD WITH (FILLFACTOR = 80, ONLINE = ON);

Ø  FILLFACTOR: Specifies the percentage of space on each leaf-level page to be filled with data, reserving the rest for future growth.

Ø  ONLINE: Allows the index to be rebuilt without significant locking, enabling concurrent access to the table.

Rebuild a Specific Index

ALTER INDEX [index_name] ON [table_name] REBUILD;

 Rebuild All Indexes on a Table

ALTER INDEX ALL ON [table_name]REBUILD;

 Rebuild All Indexes in a Database

DECLARE @TableName NVARCHAR(256); DECLARE @SQL NVARCHAR(max); DECLARE table_cursor CURSOR FOR   SELECT Quotename(Schema_name(schema_id)) + '.'          + Quotename(NAME)   FROM   sys.tables; OPEN table_cursor; FETCH next FROM table_cursor INTO @TableName; WHILE @@FETCH_STATUS = 0   BEGIN       SET @SQL = 'ALTER INDEX ALL ON ' + @TableName   + ' REBUILD;';       EXEC Sp_executesql        @SQL;       FETCH next FROM table_cursor INTO @TableName;   END; CLOSE table_cursor; DEALLOCATE table_cursor;