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, 1404🔥, 0💬
Popular Posts:
Where to find answers to frequently asked questions on Conditional Statements and Loops in SQL Serve...
Why I Can Not Enter 0.001 Second in DATETIME values in SQL Server Transact-SQL? If you enter millise...
How To Install PHP on Windows in MySQL? The best way to download and install PHP on Windows systems ...
What Is Oracle in Oracle? Oracle is a company. Oracle is also a database server, which manages data ...
How To Count Rows with the COUNT(*) Function in SQL Server? If you want to count the number of rows,...