SQL 2005+

SQL Server Indexes Part 2

by Richard Carr, published at http://www.blackwasp.co.uk/SQLIndexes2.aspx

The sixteenth part of the SQL Server Programming Fundamentals tutorial continues the review of indexes in SQL Server 2005 databases. This article expands upon an earlier discussion of indexes, implementing them with SQL Server Management Studio and T-SQL.

Adding Included Columns to an Index

To modify an index to be an index with included columns, you can use the Index Properties dialog box. To open this dialog box, expand the Contracts table in the object explorer and then expand the Indexes node in the tree structure. Right-click the IX_ContractRenewalDate index and select Properties from the menu that appears. You can see that the Index Properties dialog box provides access to the settings for the index but in a different format to the Indexes/Keys window.

To add included columns, choose the Included Columns section from the list of options on the left of the window. Click the Add button to show the list of columns that are available, then tick the checkboxes alongside the columns that you wish to be included. For our sample index, tick the ContractNumber and RenewAutomatically columns, then click OK to transfer these details back to the previous dialog box. Click OK again to apply the index updates.

Adding an Index Using Transact-SQL

Indexes can be created using the Transact-SQL script language's CREATE INDEX statement. This command creates a non-clustered index by default but can be modified to create clustered and unique indexes using additional keywords:

CREATE INDEX                    -- Non-clustered
CREATE NONCLUSTERED INDEX       -- Non-clustered
CREATE CLUSTERED INDEX          -- Clustered
CREATE UNIQUE INDEX             -- Unique, non-clustered
CREATE UNIQUE CLUSTERED INDEX   -- Unique, clustered

When you create a new index, you must specify a unique name. This is appended to the CREATE INDEX command. For example:

CREATE INDEX IX_ContractCustomerAddress

Following the name of the index, you must provide the name of the table that the index will be applied to, using an ON clause. In this case, we are modifying the Contracts table:

CREATE INDEX IX_ContractCustomerAddress ON Contracts

Finally, we need to specify the list of key columns that will be incorporated into the index. This list should be comma-separated within a pair of parentheses. The following statement is enough to generate a new index, so try executing it against the JoBS database:

CREATE INDEX IX_ContractCustomerAddress ON Contracts
(
    CustomerAddressId
)

Specifying a Filegroup

You can store the index in a different filegroup to that of its underlying table. To do so, add a second ON clause at the end of the statement and provide the name of the filegroup that you wish to use:

CREATE INDEX IX_CustomerName ON Customers
(
    LastName,
    FirstName
)
ON [PRIMARY]

Adding Included Columns

Non-key columns can be added in a second comma-separated list. This list is separated form the key columns list with the INCLUDE clause. In the following example, we are creating a new index for the Customers table with a single key column to index the name of the business. However, for performance of specific queries, we are including other columns from the table in the index:

CREATE INDEX IX_CustomerBusinessName ON Customers
(
    BusinessName
)
INCLUDE
(
    CustomerNumber,
    FirstName,
    LastName
)

Specifying Other Options

The other key properties that were described in the first half of this article can be specified using options in a WITH clause. This clause appears after the column lists and includes a pair of parentheses surrounding the options to be set. In the following example, we set a fill factor of 50% using the FILLFACTOR option.

CREATE INDEX IX_CustomerCreatedDate ON Customers
(
    CreatedDate
)
WITH (FILLFACTOR = 50)

The Pad Index option can be specified as either ON or OFF.

CREATE INDEX IX_CustomerCreatedDate ON Customers
(
    CreatedDate
)
WITH (FILLFACTOR = 50, PAD_INDEX  = ON)

There are various other options that can also be set to either ON or OFF. These include:

• IGNORE_DUP_KEY. Sets the "Ignore Duplicate Keys" option for bulk inserts.
• STATISTICS_NORECOMPUTE. Indicates whether statistics should be recomputed.

Dropping Indexes

If an index is created in error or is no longer required, it can be deleted using the DROP INDEX command. This statement requires the name of the table and index be specified, separated by a full stop (period), as in the final example:

DROP INDEX Customers.IX_CustomerCreatedDate