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, 2081🔥, 0💬
Popular Posts:
How To Update Multiple Rows with One UPDATE Statement in SQL Server? If the WHERE clause in an UPDAT...
How To Use "IF ... ELSE IF ..." Statement Structures in SQL Server Transact-SQL? "IF ... ELSE IF ......
Where to find tutorials to answer some frequently asked questions on Microsoft SQL Server Transact-S...
What Is Program Global Area (PGA) in Oracle? A Program Global Area (PGA) is a memory buffer that is ...
Where to find tutorials to answer some frequently asked questions on Microsoft SQL Server Transact-S...