![]() |
|
|
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
|
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 |
| DROP INDEX and CREATE INDEX - Removes the au_id_ind index on the authors table.
DROP INDEX authors.au_id_ind CREATE INDEX au_id_ind
|
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; CREATE INDEX au_id_ind |
| DBCC DBREINDEX - Rebuild all of the indexes on the authors table with 80% fill factor.
DBCC DBREINDEX (authors, '', 80) |
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
|
| DBCC INDEXDEFRAG - Defragments the au_id_ind index on the Authors table.
DBCC INDEXDEFRAG (Pubs, Authors, au_id_ind)
|
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; |
Next Steps
| Share: | Share | Tweet |
|
![]() |
![]() |
Free SQL Server Learning |
|
privacy | disclaimer | copyright | advertise | about authors | contribute | feedback | giveaways | user groups Some names and products listed are the registered trademarks of their respective owners. Edgewood Solutions LLC | MSSharePointTips.com | MSSQLTips.com |