Indexes, like tables, use pages. The structure of index pages is similar to the structure used for table data pages. Index pages are 8 KB (8,192 bytes) in size and have a 96-byte header. But unlike data pages, they do not have row offsets. Each index has a corresponding row in the sys.indexes catalog view with an index ID value (index_id) of 1 for clustered indexes or 2–250 for nonclustered indexes. An index ID value of 255 indicates large object data such as image, ntext, text, varchar(max), nvarchar(max), varbinary(max), or xml data. (Large object data types cannot be index key columns; varchar(max), nvarchar(max), varbinary(max), and xml data types can be included columns, however.)

SQL Server maintains indexes using a BTree structure, which is a basic tree structure consisting of a root node, intermediate level nodes, and leaf nodes. Because indexes use a tree structure, you can search them quickly and efficiently. Without the tree structure, SQL Server would need to read each table data page in the database in turn, searching for the correct record.

To put this in perspective, consider a simple table in which each data page contains a single row. In this case, if SQL Server searches for data in row 800 and there is no index, SQL Server may have to search 799 other rows before finding the right row. With a tree structure, SQL Server navigates the nodes down the index searching for the row that matches the corresponding index key. In the best-case scenario, in which the index keys have been arranged in a full tree, the number of nodes that need to be searched is proportional to the height of the tree. For example, 27,000 rows may be represented by 30 levels of nodes, and if so, SQL Server would have to navigate a maximum of 15 nodes to find the matching row.

Note

You may have noticed that I simplified this example to demonstrate the power of indexing. Nevertheless, indexing can improve performance by orders of magnitude, and accessing a database without indexing can seem extremely slow. You must be careful, however; indexing the wrong information also can make the database perform slowly, which is why it is so important to select the most referenced/used column in the table to index.

In SQL Server 2005, indexing performance has been enhanced in many ways:

SQL Server 2005 supports two types of indexing:

SQL Server 2005 also supports a special type of index for XML data. An XML index can be either a nonclustered index (the default) or a clustered index. A clustered index is created for XML data from the clustering key of the user table and an XML node identifier. Each table can have up to 249 XML indexes. You will learn more about XML indexes in the subsection titled "Using XML Indexes" later in this chapter.

You can create clustered and nonclustered indexes on almost any column. Exceptions include common language runtime (CLR) user-defined types and very large object data types—you cannot create indexes on these data types. If you want to create indexes on computed columns, you must ensure the computed column expression always returns the same result for a specific set of inputs. Although you can create an index on any other type of column, you should always select the index column carefully. Selecting the correct column to index improves response time dramatically. Selecting the wrong column to index actually can degrade response time. For more information about which column to index, use the Database Tuning Advisor.

A clustered index stores the actual table data pages at the leaf level, and the table data is physically ordered around the key. A table can have only one clustered index, and when this index is created, the following events also occur:

As a result, there are many disk I/O operations and extensive use of system and memory resources. So if you plan to create a clustered index, make sure you have enough free space, equal to at least 1.5 times the amount of data in the table. The extra free space ensures that you have enough space to complete the operation efficiently.

Normally, you create a clustered index on a primary key. You can, however, create a clustered index on any named column, such as cust_lname or cust_id. When you create a clustered index, the values you are indexing should be unique. If the values are not unique, SQL Server creates secondary sort keys on rows that have duplicates of their primary sort keys.

In a nonclustered index, pages at the leaf level contain a bookmark that tells SQL Server where to find the data row corresponding to the key in the index. If the table has a clustered index, the bookmark indicates the clustered index key. If the table does not have a clustered index, the bookmark is an actual row locator.

When you create a nonclustered index, SQL Server creates the required index pages but does not rearrange table data, and other indexes for the table are not deleted. Each table can have up to 249 nonclustered indexes.

As mentioned earlier, an XML index is a special type of index that can be either clustered or nonclustered. Before you can create an XML index, there must be a clustered index based on the primary key of the user table, and this key is limited to 15 columns. Two types of XML indexes can be created: primary and secondary. Each xml column in a table can have one primary XML index and one or more secondary XML indexes. However, there must be a primary XML index before a secondary XML index can be created on a column, and you cannot create a primary XML index on a computed xml column.

Also note that an XML index can only be created on a single xml column. You cannot create an XML index on a non-xml column, nor can you create a relational index on an xml column. You cannot create an XML index on an xml column in a view, on a table-valued variable with xml columns, or on an xml type variable. Finally, the SET options must be the same as those required for indexed views and computed-column indexes. This means ARITHABORT must be set to ON when an XML index is created and when inserting, deleting, or updating values in the xml column.

Now that you know how indexes work, you can focus on which columns you should index. Ideally, you will select columns for indexing based on the types of queries executed on the database. SQL Server Profiler can help you determine the types of queries being run. You use SQL Profiler to create a trace that contains a good snapshot of activities performed by users on the database.

You can examine this trace manually to see what types of queries are executed, or you can use the trace file as a saved workload file in the Database Engine Tuning Advisor. Regardless of which method you use, keep in mind that the maximum length of all key columns that comprise an index is 900 bytes. This means that the total size in bytes of all columns must be 900 or less. (Columns that are not part of the index key can be included, and these included columns can exceed the index size limitations of 16 key columns and maximum key size of 900 bytes.) Table 9-4 offers some guidelines about the kinds of tables and columns that can be successfully indexed and those that do not result in useful indexes.

Table 9-5 provides suggestions for the types of columns that should use clustered or nonclustered indexes.

With SQL Server 2005, you can index computed columns and views as well as tables. Indexes on computed columns and views involve storing results in the database for future reference. With computed columns, the column values are calculated and then used to build the keys stored in the index. With views, the result set is stored by creating a clustered index on the view. In both cases, the stored results are valid only if all connections referring to the results can generate an identical result set, which puts specific restrictions on how you can create indexes on computed columns and views.

You must establish connections referring to the results using specific SET options, and these options must have the same settings. The options you must set are as follows:

Furthermore, all operations referencing the view must use the exact same algorithm to build the view result set, including:

Both tables and views can have indexes. In SQL Server Management Studio, you can view indexes associated with a table or view by completing the following steps:

Using the sp_statistics stored procedure, you can examine the indexes for a specific table or view. To do this, you simply specify the name of the table or view whose indexes you want to examine, as shown in the following example:

USE OrderSystemDB
EXEC sp_statistics Sales.Customers

Only the owner of a table or view can create indexes on that table or view. You can create indexes with a wizard in the SQL Server Management Studio or with the Transact-SQL CREATE INDEX command. To create indexes with the wizard, complete the following steps:

Use the Transact-SQL CREATE INDEX command to create indexes with the syntax shown in Example 9-10.

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name 
    ON <object> ( column [ ASC | DESC ] [ ,...n ] ) 
    [ INCLUDE ( column_name [ ,...n ] ) ]  
    [ WITH ( <relational_index_option> [ ,...n ] ) ] 
    [ ON { partition_scheme_name ( column_name )      
         | filegroup_name  
         | default   
         } ]
[ ; ]
<object> ::=
{ [ database_name. [ schema_name ] . | schema_name. ]  
        table_or_view_name }

<relational_index_option> ::=
{ PAD_INDEX  = { ON | OFF } 
  | FILLFACTOR = fillfactor
  | SORT_IN_TEMPDB = { ON | OFF } 
  | IGNORE_DUP_KEY = { ON | OFF } 
  | STATISTICS_NORECOMPUTE = { ON | OFF } 
  | DROP_EXISTING = { ON | OFF } 
  | ONLINE = { ON | OFF }  
  | ALLOW_ROW_LOCKS = { ON | OFF } 
  | ALLOW_PAGE_LOCKS = { ON | OFF } 
  | MAXDOP = max_degree_of_parallelism }

CREATE [ PRIMARY ] XML INDEX index_name   
    ON <object> ( xml_column_name ) 
    [ USING XML INDEX xml_index_name       
        [ FOR { VALUE | PATH | PROPERTY } ] 
    [ WITH ( <xml_index_option> [ ,...n ] ) ]
[ ; ]

<object> ::=
{ [ database_name. [ schema_name ] . | schema_name. ]     
        table_name }

<xml_index_option> ::=
{ PAD_INDEX  = { ON | OFF } 
  | FILLFACTOR = fillfactor
  | SORT_IN_TEMPDB = { ON | OFF } 
  | STATISTICS_NORECOMPUTE = { ON | OFF }
  | DROP_EXISTING = { ON | OFF } 
  | ALLOW_ROW_LOCKS = { ON | OFF } 
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism }

After you create an index, you may need to change its properties, rename it, or delete it. You handle these tasks in SQL Server Management Studio by completing the following steps:

You can also manage indexes with the Transact-SQL commands ALTER INDEX and DROP INDEX. Unfortunately, you must use these commands cautiously because there are several limitations to these commands. For example, you cannot drop an index that was created by defining a primary key or unique constraints. You must instead drop the constraint with ALTER TABLE. Example 9-11 shows the syntax for ALTER INDEX and Example 9-12 shows the syntax for DROP INDEX.

ALTER INDEX { index_name | ALL }  
    ON <object>   
    { REBUILD [ [ WITH ( <rebuild_index_option> [ ,...n ] ) ]  
      | [ PARTITION = partition_number        
           [ WITH ( <single_partition_rebuild_index_option> [ ,...n ] 
) ] ] ]  
    | DISABLE 
    | REORGANIZE    
        [ PARTITION = partition_number ] 
        [ WITH ( LOB_COMPACTION = { ON | OFF } ) ] 
    | SET ( <set_index_option> [ ,...n ] ) }
[ ; ]

<object> ::=
{ [ database_name. [ schema_name ] . | schema_name. ]     
        table_or_view_name }

<rebuild_index_option > ::=
{ PAD_INDEX  = { ON | OFF } 
  | FILLFACTOR = fillfactor  
  | SORT_IN_TEMPDB = { ON | OFF }  
  | IGNORE_DUP_KEY = { ON | OFF }  
  | STATISTICS_NORECOMPUTE = { ON | OFF }  
  | ONLINE = { ON | OFF }  
  | ALLOW_ROW_LOCKS = { ON | OFF }  
  | ALLOW_PAGE_LOCKS = { ON | OFF }  
  | MAXDOP = max_degree_of_parallelism }

<single_partition_rebuild_index_option> ::=
{ SORT_IN_TEMPDB = { ON | OFF } 
  | MAXDOP = max_degree_of_parallelism }

<set_index_option>::=
{ ALLOW_ROW_LOCKS= { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF } }

DROP INDEX
{ <drop_relational_or_xml_index> [ ,...n ]
| <drop_backward_compatible_index> [ ,...n ] }

<drop_relational_or_xml_index> ::= 
    index_name ON <object>
    [ WITH ( <drop_clustered_index_option> [ ,...n ] ) ]

<drop_backward_compatible_index> ::= 
    [ owner_name. ] table_or_view_name.index_name

<object> ::=
{ [ database_name. [ schema_name ] . | schema_name. ]     
        table_or_view_name }

<drop_clustered_index_option> ::=
{ MAXDOP = max_degree_of_parallelism  
    | ONLINE = { ON | OFF }  
  | MOVE TO { partition_scheme_name ( column_name )     
            | filegroup_name      
            | "default"      
            } }

The Database Engine Tuning Advisor is one of the best tools a database administrator can use to facilitate the indexing and optimization process. But before you start this wizard, you should create a trace containing a representative snapshot of database activity. You will use this snapshot as the workload file in the Database Engine Tuning Advisor. For specific pointers on creating a trace file, see the subsection titled "Creating and Managing Performance Monitor Logs" in Chapter 13. To use the Database Engine Tuning Advisor, complete the following steps: