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.
Â
2016-11-08, 1607👍, 0💬
Popular Posts:
What Is a Parameter File in Oracle? A parameter file is a file that contains a list of initializatio...
What Is SQL*Plus in Oracle? SQL*Plus is an interactive and batch query tool that is installed with e...
How To Convert Character Strings into Numeric Values in SQL Server Transact-SQL? Sometimes you need ...
How To Define an External Table in a Text File in Oracle? You can use the CREATE TABLE statement to ...
How to connect SQL Server Management Studio Express to SQL Server 2005 Express in SQL Server? Once y...