Siusic Dot Com

In my previous post “What is Fragmentation and How to Defragment a Table Being Used Consistently?”

I told you that DBCC INDEXDEFRAG command was the solution to defragment in a 24/7 environment. This is only true for SQL 2000.

In SQL server 2005. There is a new way of dealing with it. When you set up a maintenance plan using the index rebuild task you will find there is an option to Keep index online while re-indexing. See the following screen shot.

When this option checked SQL server uses the following statement to rebuild indexes behind the scene

ALTER INDEX IndexName ON TableName REBUILD WITH ( ONLINE = ON ).

This is a new feature in SQL 2005. However If you run this statement you may experience error saying this feature is only available in SQL 2005 enterprise edition. Microsoft wants money from the advance feature that it provides on SQL 2005. So for standard SQL 2005 you will have to rely on DBCC INDEXDEFRAG. There is a new command to replace DBCC INDEXDEFRAG in SQL 2005 though. It is ALTER INDEX statement with the REORGANIZE clause

Example: ALTER INDEX IndexName ON TableName REORGANIZE .

Related Posts:

What is Fragmentation and How to Defragment a Table Being Used Consistently?

A Real Life Job Interview Question for SQL Database Administrator

Join to Parametrized Table Function - Cross Apply and Outer Apply in SQL Server 2005

MySQL vs SQL Server in Data File Management

Grant Truncate?