Non-unique clustered index rebuild in SQL Server 2005

One of the big disadvantages of SQL Server 2000 non-unique clustered indexes was the fact that SQL Server generated a new uniqueifier whenever you would rebuild your index. Although the general guideline remains to create narrow, unique and ever-increasing clustered indexes SQL Server 2005 has a nice improvement over SQL Server 2000.

SQL Server 2005 no longer changes the uniqueifier when you rebuild it which is great news! You now have more control over when you want to rebuild your non-clustered indexes if your table has a non-unique clustered index. This is true for ALTER INDEX, DBCC DBREINDEX as well as CREATE INDEX WITH DROP_EXISTING.

Here is a little script to show this behavior.
I check the STATS_DATE because whenever your indexes are being rebuilt your statistics will be updated with a fullscan.

CREATE TABLE myUniqueifierTest

(myID int,

myChar char(200)

)

GO

INSERT INTO myUniqueifierTest (myID, myChar)

VALUES (1, REPLICATE('X', 200))

INSERT INTO myUniqueifierTest (myID, myChar)

VALUES (1, REPLICATE('Y', 200))

INSERT INTO myUniqueifierTest (myID, myChar)

VALUES (1, REPLICATE('Z', 200))

GO

CREATE CLUSTERED INDEX myCI ON myUniqueifierTest (myID)

GO

CREATE NONCLUSTERED INDEX myNCI ON myUniqueifierTest (myChar)

GO

WAITFOR DELAY '00:00:03'

ALTER INDEX myCI ON myUniqueifierTest

REBUILD

GO

--DBCC DBREINDEX(myUniqueifierTest, myCI)

--CREATE CLUSTERED INDEX myCI ON myUniqueifierTest (myID) WITH DROP_EXISTING

SELECT STATS_DATE ( OBJECT_ID('myUniqueifierTest'), 1 )

SELECT STATS_DATE ( OBJECT_ID('myUniqueifierTest'), 2 )

GO

DROP TABLE myUniqueifierTest