Collections:
Rebuilding All Indexes on One Table in SQL Server
How To Rebuild All Indexes on a Single Table in SQL Server?
✍: FYIcenter.com
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.
⇒ DROP_EXISTING - Recreating an Existing Index in SQL Server
⇐ "ALTER INDEX ... REBUILD" - Defragmenting Indexes in SQL Server
2016-11-08, 1326🔥, 0💬
Popular Posts:
How To Verify Your PHP Installation in MySQL? PHP provides two execution interfaces: Command Line In...
What is dba.FYIcenter.com Website about? dba.FYIcenter.com is a Website for DBAs (database administr...
Where to find answers to frequently asked questions on Conditional Statements and Loops in SQL Serve...
What Are the Differences between DATE and TIMESTAMP in Oracle? The main differences between DATE and...
What Is an Oracle Instance in Oracle? Every running Oracle database is associated with an Oracle ins...