SQL 2005+

SQL Server Indexes Part 2 (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.

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.