Beginning Microsoft® SQL Server® 2012 Programming

Chapter 9

SQL Server Storage and Index Structures

WHAT YOU WILL LEARN IN THIS CHAPTER:

• The value of indexing for data retrieval
• The cost of indexing
• How indexes are structured
• Types of indexes
• Some guidelines for choosing indexes
• How to tell when you’ve chosen good indexes
• Ways to get SQL Server to tell you what indexes to use

Indexes are a critical part of your database planning and system maintenance. They provide SQL Server (and any other database system for that matter) with additional ways to look up data and take shortcuts to that data’s physical location. Adding the right index can cut huge percentages of time off your query executions. Unfortunately, too many poorly planned indexes can be ineffective or actually increase the time it takes for your query to run. Indeed, indexes tend to be one of the most misunderstood objects that SQL Server offers, and therefore, they also tend to be one of the most mismanaged.

You will be considering indexes rather closely in this chapter from both a developer’s and an administrator’s point of view, but in order to understand indexes, you also need to understand how data is stored in SQL Server. For that reason, you will start by taking a look at SQL Server’s data-storage mechanism. Once you understand what the indexes are actually trying to organize, seeing how they do so will hopefully make more sense to you.

SQL SERVER STORAGE

Data in SQL Server can be thought of as existing in something of a hierarchy of structures. The hierarchy ...