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.

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.