Answers

Question and Answer:

  Home  MS SQL Server

⟩ How To Rebuild All Indexes on a Single Table?

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:

UPDATE ggl_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'GlobalGuideLineDatabase'),

OBJECT_ID(N'ggl_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 ggl_links_url 85.750315258512

3 ggl_links_counts 84.040404040404

ALTER INDEX ALL ON ggl_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'GlobalGuideLineDatabase'),

OBJECT_ID(N'ggl_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 ggl_links_url 0.12987012987013

3 ggl_links_counts 0.44843

 135 views

More Questions for you: