Sql-server – the best option to easily fix fragmented heap indexes

performancesql serversql-server-2008-r2sql-server-2012

I have SQL Server 2008 R2 SP2 and also SQL Server 2012 SP2 on these servers.

I have a database in which there is a lot of heap indexes which are more than 90% fragmented.

What is the best option to easily fix these heap indexes?

Best Answer

Making the assumption that you are using SQL Server (because the RDBMS is going to matter here) you can do the following

ALTER TABLE tablename REBUILD

That being said you can read this article by Paul Randal as to why you shouldn't. Unless you are using your table as a staging table where you want a quick import but then clean the table out later anyway you probably want to put a clustered index on your table. At this point it won't be a heap and you have more options for dealing with fragmentation.

You generally want your CI to be small, unchanging and ever increasing (and unique is nice but not necessary). The unchanging and ever increasing will minimize any fragmentation for the future.

Related Question