Index Builds in SQL Server 2000 vs SQL Server 2005

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

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *