Chapter 44. Does the order of columns in an index matter?

 

Joe Webb

A single column index is straightforward. You may have heard it compared to the index in the back of a technical book. To find information in the book, say you want to learn more about how DBCC INPUTBUFFER is used, you look up DBCC INPUTBUFFER in the index. The index doesn’t contain the information on DBCC INPUTBUFFER; it has a pointer to the page where the command is described. You turn to that page and read about it. This is a good analogy for a single column, nonclustered index.

In Microsoft SQL Server, you can also create an index that contains more than one column. This is known as a composite index. A good analogy for a composite index is the telephone book.

Understanding the basics of composite indexes

A telephone book lists every individual in the local area who has a publicly available telephone number. It’s organized not by one column, but by two: last name and first name (ignoring the middle name that is sometimes listed but most often treated as an extension of the person’s first name).

To look up someone in the telephone book, you first navigate to the last name and then the first name. For example, to find Jake Smith, you first locate the Smiths. Then within the Smiths, you find Jake.

Finding a specific row

Finding a last name

Finding a first name

Summary

About the author