Index Builds in SQL Server 2000 vs SQL Server 2005
By: Jeremy Kadlec | Comments | Related: More > Indexing
Problem
Among many other changes between SQL Server 2000 to 2005, the index creation code has some subtle changes that are important to know and handle appropriately in scripts. It is important to be aware of these changes due to the beneficial performance gains with proper indexes. Check out the index creation differences between SQL Server 2000 and 2005.
Solution
When transitioning from SQL Server 2000 and 2005 be sure you are using the correct syntax for your next database design. Below outlines the T-SQL commands to build many of the common indexes needed to support your database design to achieve high performance and high levels of concurrency.
SQL Server 2000 | SQL Server 2005 |
CREATE INDEX - Clustered
|
CREATE INDEX - Clustered
CREATE CLUSTERED INDEX IX_dbo_Customer_CustID |
CREATE INDEX - Non-clustered
|
CREATE INDEX - Non-clustered
CREATE INDEX IX_dbo_Customer_CustName |
CREATE INDEX - Unique
|
CREATE INDEX - Unique
CREATE UNIQUE INDEX IX_unq_dbo_Customer_CustID |
CREATE INDEX - Non-clustered Composite (Multiple columns in the index)
|
CREATE INDEX - Non-clustered Composite (Multiple columns in the index)
CREATE NONCLUSTERED INDEX IX_dbo_Customer_CustID_CustName_CustZipCode |
CREATE INDEX - Non-clustered with 80% fill factor (Percentage of page full)
|
CREATE INDEX - Non-clustered with 80% fill factor (Percentage of page full)
CREATE NONCLUSTERED INDEX IX_dbo_Customer_CustID |
CREATE INDEX - Non-clustered ascending order
|
CREATE INDEX - Non-clustered ascending order
CREATE NONCLUSTERED INDEX IX_dbo_Customer_CustID |
CREATE INDEX - Non-clustered descending order
|
CREATE INDEX - Non-clustered descending order
CREATE NONCLUSTERED INDEX IX_dbo_Customer_CustID8 |
CREATE INDEX - IGNORE_DUP_KEY
CREATE UNIQUE INDEX au_id_unq_ind |
CREATE INDEX - IGNORE_DUP_KEY
CREATE UNIQUE INDEX AK_Customer_CustID |
CREATE INDEX - DROP_EXISTING
CREATE INDEX au_id_ind |
CREATE INDEX - DROP_EXISTING
CREATE NONCLUSTERED INDEX AK_Customer_CustID |
CREATE INDEX - STATISTICS_NORECOMPUTE
CREATE INDEX au_id_ind |
CREATE INDEX - STATISTICS_NORECOMPUTE
CREATE NONCLUSTERED INDEX IDX_Customer_CustID |
CREATE INDEX - SORT_IN_TEMPDB
CREATE INDEX au_id_ind |
CREATE INDEX - SORT_IN_TEMPDB
CREATE NONCLUSTERED INDEX IDX_Customer_CustID |
CREATE INDEX - MAXDOP
Not available. |
CREATE INDEX - MAXDOP
CREATE NONCLUSTERED INDEX IDX_Customer_CustID11 |
Next Steps
-
As you begin to adopt SQL Server 2005, be aware that the index creation code has some subtle differences.
-
If you rely on deployment scripts to promote code between development, test and production, be sure that the upgraded scripts are correct. Then double check the SQL Server 2005 output and final objects to ensure they are deployed as you expect.
-
Check out the index related tips on MSSQLTips.com:
About the author

View all my tips