Learn more about SQL Server tools

   
   























Latest from MSSQLTips














Index Rebuilds in SQL Server 2000 vs SQL Server 2005

MSSQLTips author Jeremy Kadlec By:   |   Read Comments   |   Related Tips: 1 | 2 | 3 | 4 | 5 | 6 | More > Fragmentation and Index Maintenance

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.


Last Update: 7/12/2006





About the author
MSSQLTips author Jeremy Kadlec
Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an Edgewood Solutions SQL Server Consultant, MSSQLTips.com co-founder and Baltimore SSUG co-leader.

View all my tips


print tip Print  
Become a paid author


Learn more about SQL Server tools








Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     





 
More SQL Server Solutions



 

Follow

Get Free SQL Tips

Twitter

LinkedIn

Google+

Facebook

Pinterest

RSS

Learning

DBAs

Developers

BI Professionals

Careers

Q and A

Today's Tip

Resources

Tutorials

Webcasts

Whitepapers

Tools

Search

Tip Categories

Search By TipID

Authors

Community

First Timer?

Pictures

Free T-shirt

Contribute

Events

User Groups

Author of the Year

More Info

Join

About

Copyright

Privacy

Disclaimer

Feedback

Advertise

Copyright (c) 2006-2015 Edgewood Solutions, LLC All rights reserved
Some names and products listed are the registered trademarks of their respective owners.