Database Reference
In-Depth Information
You can use the DROP_EXISTING clause for both clustered and nonclustered indexes, and even to convert a
nonclustered index to a clustered index. However, you can't use it to convert a clustered index to a nonclustered index.
The drawbacks of this defragmentation technique are as follows:
Blocking : Similar to the DROP and CREATE methods, this technique also causes and faces
blocking from other queries accessing the table (or any index on the table).
Index with constraints : Unlike the first method, the CREATE INDEX statement with
DROP_EXISTING can be used to re-create indexes with constraints. If the constraint is a primary
key or the unique constraint is associated with a foreign key, then failing to include the UNIQUE
keyword in the CREATE statement will result in an error like this:
msg 1907, Level 16, State 1, Line 1 Cannot recreate index 'pK_name'. the new index definition does not match
the constraint being enforced by the existing index.
Note
Table with multiple fragmented indexes : As table data fragments, the indexes often become
fragmented as well. If this defragmentation technique is used, then all the indexes on the table
have to be identified and rebuilt individually.
You can avoid the last two limitations associated with this technique by using ALTER INDEX REBUILD ,
as explained next.
Executing the ALTER INDEX REBUILD Statement
ALTER INDEX REBUILD rebuilds an index in one atomic step, just like CREATE INDEX with the DROP_EXISTING clause.
Since ALTER INDEX REBUILD also rebuilds the index physically, it allows SQL Server to assign fresh pages to reduce
both internal and external fragmentation to a minimum. But unlike CREATE INDEX with the DROP_EXISTING clause, it
allows an index (supporting either the PRIMARY KEY or UNIQUE constraint) to be rebuilt dynamically without dropping
and re-creating the constraints.
To understand the use of ALTER INDEX REBUILD to defragment an index, consider the fragmented table used in
the “Fragmentation Overhead” and “Analyzing the Amount of Fragmentation” sections. This table is repeated here:
IF (SELECT OBJECT_ID('Test1')
) IS NOT NULL
DROP TABLE dbo.Test1 ;
GO
CREATE TABLE dbo.Test1
(C1 INT,
C2 INT,
C3 INT,
c4 CHAR(2000)
) ;
CREATE CLUSTERED INDEX i1 ON dbo.Test1 (C1) ;
WITH Nums
AS (SELECT 1 AS n
UNION ALL
SELECT n + 1
 
 
Search WWH ::




Custom Search