Index Rebuilds in SQL Server 2000 vs SQL Server 2005

Index Rebuilds in SQL Server 2000 vs SQL Server 2005
Below outlines the index rebuilding code changes from SQL Server 2000 to 2005 with the purpose of the code and a sample.

CREATE INDEX with DROP_EXISTING - Creates a new index with the same name and drops the current index while ensuring the nonclustered indexes are not rebuilt twice.

SQL Server 2000:
CREATE CLUSTERED INDEX au_id_clidxON Authors (au_id) WITH DROP_EXISTING GO

SQL Server 2005:
CREATE CLUSTERED INDEX au_id_clidxON dbo.Authors (au_id) WITH (DROP_EXISTING = ON);GO



Sql Server 2000:
DBCC DBREINDEX - Rebuild all of the indexes on the authors table with 80% fill factor.
DBCC DBREINDEX (authors, '', 80) GO

Sql Server 2005:
ALTER INDEX - Rebuild all of the indexes on the Authors table with 80% fill factor, sort the intermediary data in TempDB and automatic updating of the statistics are enabled.
ALTER INDEX ALL ON AuthorsREBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = OFF); GO



Sql Server 2000:
DBCC INDEXDEFRAG - Defragments the au_id_ind index on the Authors table.
DBCC INDEXDEFRAG (Pubs, Authors, au_id_ind)GO

Sql Server 2005:
ALTER INDEX - Defragment the au_id_ind index on the Authors table which is intended to be a truly online operation.
ALTER INDEX au_id_ind ON dbo.Authors REORGANIZE; GO