SQL 2005+

SQL Server Indexes Part 2

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.

Creating Indexes

This article is a continuation of the description of implementing indexes in SQL Server 2005. The first part, "SQL Server Indexes Part 1" described indexes in general and the types of index that are available. In this article we will create indexes using the SQL Server Management Studio (SSMS) user interface tools and Transact-SQL (T-SQL) script commands.

We will add new indexes to tables in the JoBS tutorial database. This has been created over the course of the tutorial so far. To create the tutorial database, download and run the script using the link at the top of this page. This will create the database and some sample data. If you have been following the tutorial, you may want to delete your existing tutorial and create a fresh version using the same script.

Adding an Index Using SQL Server Management Studio

Indexes can be created and updated using the SQL Server Management Studio's user interface, using the same dialog boxes as for unique constraints. We will start by using these tools to create a new index for the Contracts table in the JoBS database. This index will improve the performance of queries that filter the contract data according to each row's renewal date.

To begin, right-click the Contracts table in the SSMS Object Explorer. From the context-sensitive menu that appears, select "Design". The table designer window and associated Table Designer menu will be displayed. To begin the process of adding an index, select "Indexes/Keys" from the new menu, or click the appropriate toolbar button. This shows the Indexes/Keys dialog box.

The dialog box will already include an index that represents the primary key for the table. To add the new index, click the Add button. Ensure that the new index is selected before modifying any of its properties so that you do not accidentally adjust the settings for the primary key. We can now change the behaviour of the index using the categorised list of properties at the right-hand side of the dialog box.

General Options

• Columns. The Columns property defines which of the table's columns will be indexed. To change the columns, select the property and click the lookup button that appears. You can then choose one or more columns and the ordering of each using the Index Columns dialog box that was explained in an earlier article. For the new index, change the column to "RenewalDate".
• Is Unique. This option allows you to create a unique index. If set to "Yes", no duplicate renewal dates will be permitted. In our example, we will want to allow two or more customers to renew their contracts on the same date so leave this option as "No".
• Type. This property allows you to create either a unique key or an index. Ensure that this option is set to "Index".

Identity Options

• Name. The Name property allows you to create a unique name for the index. Replace the provided name with "IX_ContractRenewalDate".
• Description. The Description option allows you to add some comments to the index. This property does not modify the behaviour of the index but can be useful when other developers or database administrators are viewing the index definition.

Table Designer Options

Some of the table designer options are beyond the scope of this article. The important items are listed below.

• Create As Clustered. This property determines whether the new item will be a clustered index or a non-clustered index. This option will be unavailable because the primary key index that already exists is clustered and only one clustered index per table is permitted.
• Data Space Specification. This property can be expanded to show three sub-options. These options determine how the data in the index is stored. Firstly, you can select whether to hold the data in a filegroup or partition scheme. Partitions are beyond the scope of this article so the key property is the one that allows you to select the filegroup that you wish to use.
• Fill Specification. The fill specification contains two sub-options, "Fill Factor" and "Pad Index". The fill factor determines the percentage of index leaf nodes that will be filled by the index when it is first created. For example, if you specify a fill factor of 100, the index will be completely filled. This will mean that the index will be the smallest it can possibly be. However, it will include no empty space for new entries so when new rows are added to the table, the index's data pages will be split, lowering performance. A lower fill factor increases the size of the index's storage requirements but gives better performance for write operations. It also lowers the performance of read operations that use the index. For our index, we will use the default value, which creates an almost-full index.
• Pad Index. If a fill factor is specified, the pad index option can be set to Yes to specify that new index pages should be created with the same fill factor as the original index.
• Ignore Duplicate Keys. This option is only available for unique indexes and only operates during bulk insert operations. If the source data for a bulk insert contains non-unique data for the index columns, you can specify "Yes" to error when the duplicate information is encountered and roll back the entire set of data, or "No" to ignore the single insert, show a warning and continue with the import.
• Included Columns. This property shows the non-key columns that have been added to an index with included columns. Initially this will be blank.
• Re-Compute Statistics. This option specifies whether the database engine should recompute statistics during the index creation. This slows the speed of creation of the index but can improve its overall performance.

After setting the properties, close the dialog box, then close the table designer, saving the updates to the table.

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:

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:

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: