How To Rebuild All Indexes on a Single Table in SQL Server?

If you have several indexes on a single table and want to rebuild all of them, you may use the "ALTER INDEX ALL ON table_name REBUILD" statement as shown in the tutorial exercise below:

USE FyiCenterData;
GO

UPDATE fyi_links_indexed 
   SET url = REVERSE(url), counts = -counts
   WHERE id <=50000;
GO
(50000 row(s) affected)

SELECT i.index_id, i.name, s.avg_fragmentation_in_percent
   FROM sys.dm_db_index_physical_stats (
      DB_ID(N'FyiCenterData'), 
      OBJECT_ID(N'fyi_links_indexed'),
      DEFAULT, DEFAULT, DEFAULT) s, sys.indexes i
   WHERE s.object_id = i.object_id
      AND s.index_id = i.index_id;
GO
0        NULL             0.574712643678161
2        fyi_links_url    85.750315258512
3        fyi_links_counts 84.040404040404

ALTER INDEX ALL ON fyi_links_indexed REBUILD;
GO

SELECT i.index_id, i.name, s.avg_fragmentation_in_percent
   FROM sys.dm_db_index_physical_stats (
      DB_ID(N'FyiCenterData'), 
      OBJECT_ID(N'fyi_links_indexed'),
      DEFAULT, DEFAULT, DEFAULT) s, sys.indexes i
   WHERE s.object_id = i.object_id
      AND s.index_id = i.index_id;
GO
0        NULL             0.574712643678161
2        fyi_links_url    0.12987012987013
3        fyi_links_counts 0.448430493273543

Both indexes were defragmented to a very low level now.

 

⇒Understanding and Managing Indexes in SQL Server

⇒⇒SQL Server Database Tutorials