Stairway to SQL Server Indexes

Indexes are fundamental to database design, and tell the developer using the database a great deal about the intentions of the designer. Unfortunately indexes are too often added as an afterthought when performance issues appear. Here at last is a simple series of articles that should bring any database professional rapidly "up to speed" with them.

• An Introduction to SQL Server Indexes: Stairway to SQL Server Indexes Level 1

  Indexes are the database objects that enable SQL Server to satisfy each data access request from a client application with the minimum amount of effort, resulting in the maximum performance of individual requests while also reducing the impact of one request upon another. Prerequisites: Familiarity with the following relational database concepts: Table, row, primary key, foreign key

• Deeper into Nonclustered Indexes: Stairway to SQL Server Indexes Level 2

  By this stage, you should be familiar with the basics of SQL Server indexes. We've discussed what an Index actually is, as well as some of the most common types you're likely to encounter. Now that we've seen some simple examples of how Indexes can be useful, we're going to delve deeper into nonclustered indexes, as we'll see how they can improve the performance of more complex queries.

• Clustered Indexes: Stairway to SQL Server Indexes Level 3

  Now that we've seen the basics of indexing, and taken a deeper dive into Nonclustered Indexes, this Level will focus on searching the table, which will, in turn, will lead us to a discussion of clustered indexes.

• Pages and Extents: Stairway to SQL Server Indexes Level 4

  We've now seen how indexed and non-indexed tables perform in queries, and established "logical reads" as the metric for query performance. Now it is time to explain why logical reads are an excellent metric and also explain what is actually being read.

• Indexes with Included Columns: Stairway to SQL Server Indexes Level 5

  Included columns enable nonclustered indexes to become covering indexes for a variety of queries, improving the performance of those queries and with the only overhead being an increase the size of an index.

• Bookmarks in SQL Server Indexes: Stairway to SQL Server Indexes Level 6

  A nonclustered index entry consists of search key columns, included columns, and the bookmark. The bookmark value will be either a RID or the clustered index's key, depending upon whether the table is a heap or a clustered index. Choosing the best clustered index for a table requires that you follow three guidelines to ensure that the index key will make a good bookmark.

• Filtered Indexes in SQL Server: Stairway to SQL Server Indexes Level 7

  A filtered index eliminates unusable entries from the index, producing an index that is as beneficial as, but much smaller than, an unfiltered index. If a certain subset of a table's data is frequently requested, a filtered index can also be a covering index; resulting in a considerable reduction in IO.

• Unique Indexes in SQL Server: Stairway to SQL Server Indexes Level 8

  Indexes that ensure data integrity in addition to locating rows.

• Reading Query Plans: Stairway to SQL Server Indexes Level 9

  Determining how, and if, SQL Server is using your indexes.

• Index Internal Structure in SQL Server: Stairway to SQL Server Indexes Level 10

  A detailed look the B-Tree structure – Leaf level and non-leaf level.

• Index Fragmentation in SQL Server: Stairway to SQL Server Indexes Level 11

  Internal and external fragmentation – Causes, cures, and when not to care

• Create, Alter, and Drop Indexes: Stairway to SQL Server Indexes Level 12

  Options and impacts when creating, altering and dropping an index

• Insert, Update, and Delete Indexes: Stairway to SQL Server Indexes Level 13

  What happens to your indexes during normal database activity?

• Index Statistics in SQL Server: Stairway to SQL Server Indexes Level 14

  A look at the information that helps SQL Server make better decisions.

• SQL Server Indexing Best Practices: Stairway to SQL Server Indexes Level 15

  Dos and don’ts that lead to a healthy, well-performing database.