solving sql server problems for millions of dbas and developers since 2006

SQL Product Highlight

Idera - SQL safe backup

Idera's SQL safe provides a high-performance backup and recovery solution for Microsoft SQL Server. SQL safe saves money by reducing database backup time by up to 50% over native backups and reducing backup disk space requirements by up to 95%. SQL safe also enables complete "hands-free" automated backup of your entire SQL Server infrastructure and ensures compliance with your organization's backup and recovery policies. From implementations with tens of SQL servers to enterprises with hundreds of servers spread around the globe, SQL safe is the only SQL Server backup and recovery solution that scales to meet the challenge.

Learn more!

Index Rebuilds in SQL Server 2000 vs SQL Server 2005

By: Jeremy Kadlec | Read Comments | Print

Jeremy is the CTO @ Edgewood Solutions, co-founder of MSSQLTips.com and SQL Server MVP since 2009.

Related Tips: More

Problem
Index rebuilds are a core component for database maintenance which ultimately improve performance and the user experience with the application. DBAs and Developers alike have become accustom to much of the syntax in SQL Server 2000 and know the purpose for each set of code. In SQL Server 2005, the original syntax to support index maintenance is still supported, but will be removed in a future version of SQL Server. As such, now is the time to get up to speed on the new syntax to start to incorporate this code into your environment.

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

SQL Server 2000	SQL Server 2005
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. CREATE CLUSTERED INDEX au_id_clidx ON Authors (au_id) WITH DROP_EXISTING GO	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. CREATE CLUSTERED INDEX au_id_clidx ON dbo.Authors (au_id) WITH (DROP_EXISTING = ON); GO
DROP INDEX and CREATE INDEX - Removes the au_id_ind index on the authors table. DROP INDEX authors.au_id_ind GO CREATE INDEX au_id_ind ON Authors (au_id ASC) GO	DROP INDEX and CREATE INDEX - Removes the au_id_ind index on the authors table, which is the equal functionality as SQL Server 2000. DROP INDEX authors.au_id_ind; GO CREATE INDEX au_id_ind ON Authors (au_id ASC); GO
DBCC DBREINDEX - Rebuild all of the indexes on the authors table with 80% fill factor. DBCC DBREINDEX (authors, '', 80) GO	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 Authors REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = OFF); GO
DBCC INDEXDEFRAG - Defragments the au_id_ind index on the Authors table. DBCC INDEXDEFRAG (Pubs, Authors, au_id_ind) GO	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

Next Steps

Review your existing maintenance scripts and begin to convert your scripts from the SQL Server 2000 syntax to the 2005 syntax.
During the conversion, determine if you have an opportunity to enhance your maintenance scripts.
As you build new indexes determine your naming standard for consistency across your environment.

Related Tips: More | Become a paid author

Last Update: 7/12/2006

Share:

Free SQL Server Learning

Comments and Feedback:

Sponsor Information

"SQL diagnostic manager delivers response in minutes, not hours!"

Time-strapped database professionals use SQL Monitor to look after their servers. Try it online.

Need SQL Server help and not sure where to turn? Reach out to the Edgewood consultants for a Health Check.

Free Trial: Get Proactive Insight with Spotlight® for SQL Server Enterprise.

Solving SQL Server problems for millions of DBAs and Devs since 2006. Join now.

Valuable SQL Server web casts on Performance Tuning, Development, Administration, SSIS and more...