Index Builds in SQL Server 2000 vs SQL Server 2005

By:   |   Updated: 2006-09-07   |   Comments   |   Related: > 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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Jeremy Kadlec Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2006-09-07

Comments For This Article

















get free sql tips
agree to terms