Which is the best index design guide for SQL Server?
This SQL Server index design guide contains information on index architecture, and best practices to help you design effective indexes to meet the needs of your application. This guide assumes the reader has a general understanding of the index types available in SQL Server. For a general description of index types, see Index Types.
Can a database administrator design a good index?
Experienced database administrators can design a good set of indexes, but this task is very complex, time-consuming, and error-prone even for moderately complex databases and workloads. Understanding the characteristics of your database, queries, and data columns can help you design optimal indexes.
Why are narrow indexes good for SQL Server?
The selection of the right indexes for a database and its workload is a complex balancing act between query speed and update cost. Narrow indexes, or indexes with few columns in the index key, require less disk space and maintenance overhead.
Can a table have more than one clustered index?
There can only be one clustered index per table, because the data rows themselves can only be sorted in one order. With few exceptions, every table should have a clustered index defined on the column, or columns, that offer the following: Can be used for frequently used queries. Provide a high degree of uniqueness.
How are index levels organized in SQL Server?
In SQL Server, indexes are organized as B-Trees. Each page in an index B-tree is called an index node. The top node of the B-tree is called the root node. The bottom nodes in the index are called the leaf nodes. Any index levels between the root and the leaf nodes are collectively known as intermediate levels.
How to see which indexes the query optimizer uses?
To see which indexes the query optimizer uses for a specific query, in SQL Server Management Studio, on the Query menu, select Include Actual Execution Plan. Do not always equate index usage with good performance, and good performance with efficient index use.