Interviews: Covering index

Performance seems to be a part of every DBAs job. It should be a part of every developer’s job, but I understand the focus to build software is different and many developers aren’t sure how much a feature or option they work on will be used. They don’t think too much about performance because at small scales, perhaps performance doesn’t matter a lot these days with multiple cores and many GB of RAM. I’d disagree with that, but I understand the perspective.

What’s interesting to me is that in many interviews I’ve had over the years, I’ve not often been quizzed on specific scenarios that might help improve performance for queries. It seems many interviewers like to ask trivia questions such as the difference between a clustered and non-clustered index or what configuration knobs should I change. Code questions might be how to find duplicates in a table or deal with NULLs in aggregates.

I hope that’s changing and more interviewers are giving candidates scenarios, and looking for ways to ensure that candidates think about performance. I hope someone asks how a candidate looks at an execution plan or structures code to remove RBAR concerns. I wondered about this since I chatted with someone recently that had been asked about covering indexes in an interview and was struggling to understand how a covering index might be different than a clustered, nonclustered, filtered, or other type of index.

This might seem like a trick question, and perhaps it is for some interviewers. After all, if you don’t know, you might correctly guess what this means, but will you know this isn’t a special type of index? Perhaps you’ll try to bluff your way through the answer and hope you get close.

Answering interview questions should be a discussion, as your answer can reveal as much about how you work as it can about your knowledge. Do you probe to understand the boundaries of the question or confirm that you know what’s being asked? Do you admit when you don’t know something or perhaps reason out loud? Do you give details that make sense, or do you launch into a lecture?

In this case, you should understand that this index covers a query. This isn’t a type of index, but rather a characteristic of an index against a particular query (or set of queries). To learn more, you might want to read a bit about adding included columns or how to evaluate your choice. If you don’t know much about indexing, get started with an introduction or go through our Stairway to Indexing.

Future versions of SQL Server will include automatic index tuning, but I think we’ll always need to balance the number of indexes we have. We’ll also often want to build some indexes that will ensure the system works fairly well when it’s first installed. That means data professionals (DBAs ans developers) need to understand how indexes work and ensure they can choose a good index most of the time.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 3.8MB) podcast or subscribe to the feed at iTunes and Libsyn.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Editorial and tagged , . Bookmark the permalink.