| 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 |
