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.