SQL Server Interview Topic 18: Index Deep Dive

This topic checks whether you understand indexes beyond the definition. Interviewers often use index questions to test real project thinking.

🎯 Interview Goal

You should be able to explain when an index helps, when it hurts, and how to choose index columns.

Q125. How do you choose columns for an index?

Quick interview answer:

Choose columns used often in WHERE, JOIN, ORDER BY, and search filters. Do not index every column. Indexes should support real queries used by the application.

Study in detail: Indexes and Performance - This article explains useful index choices.

Q126. What is a composite index?

Quick interview answer:

A composite index is an index on more than one column. It is useful when queries filter or sort by those columns together, such as ClassId and IsActive.

Study in detail: Indexes and Performance - This lesson explains index patterns.

Q127. What is a covering index?

Quick interview answer:

A covering index contains all columns needed by a query, so SQL Server can answer the query from the index itself. This can improve performance, but it increases index size.

Study in detail: Performance Case Studies - This article explains practical optimization.

Q128. Can indexes slow down performance?

Quick interview answer:

Yes. Indexes can make reads faster, but inserts, updates, and deletes slower because SQL Server must update the indexes too. Too many indexes also increase storage and maintenance cost.

Study in detail: Indexes and Performance - This lesson explains index trade-offs.

Q129. What is index maintenance?

Quick interview answer:

Index maintenance means keeping indexes healthy as data changes. In production, DBAs may rebuild or reorganize indexes when needed. Developers should mainly avoid creating unnecessary indexes.

Study in detail: Monitoring and Performance - This article explains production performance checks.

💡 Interview Tip

Always connect index answers to a query. Say which query became faster and why.