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, 1317🔥, 0💬
Popular Posts:
How To Divide Query Output into Multiple Groups with the GROUP BY Clause in SQL Server? Sometimes, y...
What Is an Oracle Instance in Oracle? Every running Oracle database is associated with an Oracle ins...
Where to find answers to frequently asked questions on CREATE, ALTER and DROP Statements in MySQL? H...
How To Look at the Current SQL*Plus System Settings in Oracle? If you want to see the current values...
Where to find answers to frequently asked questions on Managing Security, Login and User in SQL Serv...