Collections:
"ALTER INDEX ... REBUILD" - Defragmenting Indexes in SQL Server
How To Rebuild Indexes with ALTER INDEX ... REBUILD in SQL Server?
✍: FYIcenter.com
When an index is defragmented to a large percentage, like > 30%, you can use the "ALTER INDEX ... REBUILD" statement to rebuild the index. Here is a tutorial exercise on rebuilding indexes:
USE FyiCenterData; GO UPDATE fyi_links_indexed SET url = REVERSE(url) 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.0142045454545 3 fyi_links_counts 0.448430493273543 ALTER INDEX fyi_links_url 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 3 fyi_links_counts 0.448430493273543
Rebuilding an index brings the fragmentation level to 0% in this case.
⇒ Rebuilding All Indexes on One Table in SQL Server
⇐ "ALTER INDEX ... REORGANIZE" - Defragmenting Indexes in SQL Server
2016-11-08, 2079🔥, 0💬
Popular Posts:
What Is a Dynamic Performance View in Oracle? Oracle contains a set of underlying views that are mai...
How REAL and FLOAT Literal Values Are Rounded in SQL Server Transact-SQL? By definition, FLOAT(n) sh...
How REAL and FLOAT Literal Values Are Rounded in SQL Server Transact-SQL? By definition, FLOAT(n) sh...
How To Provide Default Values to Function Parameters in SQL Server Transact-SQL? If you add a parame...
How To Insert New Line Characters into Strings in SQL Server Transact-SQL? If you want to break a st...