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