One of the more common myths that pertain to indexes is that they guarantee the output order of results from queries. This is not correct. As previously described in this book, the purpose of indexes is to provide an efficient access path to the data. That purpose does not guarantee the order in which the data will be accessed. The trouble with this myth is that, oftentimes, SQL Server will appear to maintain order when queries are executed under certain conditions, but when those conditions change, the execution plans change, and the results are returned in the order that the data is processed versus the order that the end user might desire.
One of the more pervasive myths commonly held is the idea that a clustered index stores the records in a table in their physical order when on disk. This myth seems to be primarily driven by confusion between what is stored on a page and where records are stored on those pages. There is a difference between data pages and records. As a refresher, you’ll see a simple demonstration that dispels this myth.
SQL Server Index Myths
Myths result in an unnecessary burden when attempting to build an index. Knowing the myths associated with indexes can prevent you from using indexing strategies that will be counterproductive.
One of the problems that people encounter when building databases and indexes is dealing with myths. Indexing myths originate from many different places. Some come from previous versions of SQL Server and its tools or are based on former functionality. Others come from the advice of others, based on conditions in a specific database that don’t match those of other databases.
The trouble with indexing myths is that they cloud the water of indexing strategies. In situations where an index can be built to resolve a serious performance issue, a myth can sometimes prevent the approach from being considered. Throughout the next few sections, I’ll cover a number of myths regarding indexing and do my best to dispel them.
The following are the indexing myths discussed in this chapter:
- Databases don’t need indexes.
- Primary keys are always clustered
- Online index operations don’t block
- Any column can be filtered in multi-column indexes
- Clustered indexes store records in physical order.
- Indexes always output in the same order.
- Fill factor is applied to indexes during inserts
- Deleting form heaps results in unrecoverable space
- Every table should be a heap or have a clustered index
When reviewing myths, it’s also a good idea to take a look at best practices. Best practices are like myths in many ways, in the sense that they are commonly held beliefs. The primary difference is that best practices stand up to scrutiny and are useful recommendations when building indexes. This chapter will examine the following best practices:
- Use clustered indexes on primary keys by default.
- Balance index count.
- Properly target database level fill factors.
- Properly target index level fill factors.
- Index foreign key columns.
- Index to your environments
When it comes to indexing, Microsoft has two tools built into SQL Server that can be used to help identify indexes that can improve database performance. These are the missing index dynamic management objects (DMOs) and the Database Engine Tuning Advisor (DTA- Not covered in this article). Both tools are useful to assist with indexing databases and can provide valuable input when working on tuning a database.