Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Index Builds in SQL Server 2000 vs SQL Server 2005


By:   |   Last Updated: 2006-09-07   |   Comments   |   Related Tips: 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 CLUSTERED INDEX au_id_clidx
ON Authors (au_id)
GO

CREATE INDEX - Clustered

CREATE CLUSTERED INDEX IX_dbo_Customer_CustID
ON dbo.Customer(CustID);
GO

CREATE INDEX - Non-clustered

CREATE INDEX au_id_ncidx
ON Authors (au_id)
GO

CREATE INDEX - Non-clustered

CREATE INDEX IX_dbo_Customer_CustName
ON dbo.Customer(CustName);
GO

CREATE INDEX - Unique

CREATE UNIQUE INDEX au_id_unidx
ON Authors (au_id)
GO

CREATE INDEX - Unique

CREATE UNIQUE INDEX IX_unq_dbo_Customer_CustID
ON dbo.Customer(CustID);
GO

CREATE INDEX - Non-clustered Composite (Multiple columns in the index)

CREATE UNIQUE INDEX au_id_au_lname_ncidx
ON Authors (au_id, au_lname)
GO

CREATE INDEX - Non-clustered Composite (Multiple columns in the index)

CREATE NONCLUSTERED INDEX IX_dbo_Customer_CustID_CustName_CustZipCode
ON dbo.Customer(CustID, CustName, CustZipCode);
GO

CREATE INDEX - Non-clustered with 80% fill factor (Percentage of page full)

CREATE INDEX au_id_ncidx
ON Authors (au_id)
WITH FILLFACTOR = 80
GO

CREATE INDEX - Non-clustered with 80% fill factor (Percentage of page full)

CREATE NONCLUSTERED INDEX IX_dbo_Customer_CustID
ON dbo.Customer(CustID)
WITH (FILLFACTOR = 80);
GO

CREATE INDEX - Non-clustered ascending order

CREATE INDEX au_id_ncidx
ON Authors (au_id ASC)
GO

CREATE INDEX - Non-clustered ascending order

CREATE NONCLUSTERED INDEX IX_dbo_Customer_CustID
ON dbo.Customer (CustID ASC);
GO

CREATE INDEX - Non-clustered descending order

CREATE INDEX au_id_ncidx
ON Authors (au_id DESC)
GO

CREATE INDEX - Non-clustered descending order

CREATE NONCLUSTERED INDEX IX_dbo_Customer_CustID8
ON dbo.Customer (CustID DESC);
GO

CREATE INDEX  - IGNORE_DUP_KEY

CREATE UNIQUE INDEX au_id_unq_ind
ON Authors(au_id)
WITH IGNORE_DUP_KEY
GO

CREATE INDEX - IGNORE_DUP_KEY

CREATE UNIQUE INDEX AK_Customer_CustID
ON dbo.Customer(CustID)
WITH (IGNORE_DUP_KEY = ON);
GO

CREATE INDEX  -  DROP_EXISTING

CREATE INDEX au_id_ind
ON Authors(au_id)
WITH DROP_EXISTING
GO

CREATE INDEX -  DROP_EXISTING

CREATE NONCLUSTERED INDEX AK_Customer_CustID
ON dbo.Customer(CustID)
WITH (DROP_EXISTING = ON);
GO

CREATE INDEX  - STATISTICS_NORECOMPUTE

CREATE INDEX au_id_ind
ON Authors(au_id)
WITH STATISTICS_NORECOMPUTE
GO

CREATE INDEX - STATISTICS_NORECOMPUTE

CREATE NONCLUSTERED INDEX IDX_Customer_CustID
ON dbo.Customer(CustID)
WITH (STATISTICS_NORECOMPUTE = ON);
GO

CREATE INDEX  - SORT_IN_TEMPDB

CREATE INDEX au_id_ind
ON Authors(au_id)
WITH SORT_IN_TEMPDB
GO

CREATE INDEX - SORT_IN_TEMPDB

CREATE NONCLUSTERED INDEX IDX_Customer_CustID
ON dbo.Customer(CustID)
WITH (SORT_IN_TEMPDB = ON);
GO

CREATE INDEX - MAXDOP

Not available.

CREATE INDEX - MAXDOP

CREATE NONCLUSTERED INDEX IDX_Customer_CustID11
ON dbo.Customer(CustID)
WITH (MAXDOP = 4);
GO

Next Steps



Last Updated: 2006-09-07


get scripts

next tip button



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

View all my tips
Related Resources




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

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools