Interview Questions for Developers — Plain-Word Answers, One Question at a Time

Interview questions stop being scary the moment someone explains them from the very beginning, in plain words. That is exactly what this page does. Every Monday and Wednesday we take one real interview question for developers, explain it from first principles — no assumed background, no jargon before its time — and finish with the short version you can actually say out loud in the interview room. Every answer also comes with a two-minute video, linked right under the question.

Bookmark this page and come back: it grows by one question every Monday and Wednesday, and the newest question always sits at the top. Everything here is free, in full — no sign-up, no gated answers.

The questions so far

Q1. Clustered vs non-clustered index — what's the actual difference?

Topic: SQL Server · From Interview Question Short #1, published 16 November 2026

Start with the table itself. A table is rows of data, stored on pages. When the database needs one particular row, it has two choices: read every row one by one until it finds the right one — slow — or use an index, something that helps it jump straight to the row it needs. Both index types in this question do that job. The real difference is where the data lives.

A clustered index is the table, sorted. It does not point at the rows from somewhere else — it is the rows, stored in the order of the index key. Think of the pages of a book, printed in chapter order: page 40 simply comes after page 39, because that is where it physically sits. And because the data rows themselves can only be sorted in one order, a table can have exactly one clustered index.

A non-clustered index is a separate list. It holds the values it sorts by, in order — and next to each value, a pointer to the real row back in the table. That is exactly the index at the back of a book: a topic, then a page number. The book's pages never move, and one book can carry several different lists at the back. A SQL Server table can have up to 999 non-clustered indexes.

Now the detail interviewers love to probe: what exactly is that pointer? Its proper name is a row locator. If the table has a clustered index, the pointer is the row's clustering key value — the non-clustered index looks up the key, and the clustered index leads to the row. If the table has no clustered index, the table is called a heap, and the pointer is a row ID — the row's exact physical address.

Clustered indexNon-clustered index
What it isThe table itself — rows stored in key orderA separate sorted list, apart from the table
How it reaches a rowThe row is right there, in orderKey plus a pointer (the row locator) to the row
How many per tableExactly oneMany — up to 999 in SQL Server

The interview version — say it like this (about 20 seconds):

"A clustered index is the table: its leaf level is the data rows, stored in key order, so there can be only one. A non-clustered index is separate: it stores the key plus a row locator — the clustering key, or a row ID on a heap — so a table can have many."

Your turn: a table is stored in order by order date. A second index sorts customers by name, with pointers back to the rows. Which one is the non-clustered index — and why? Answer: the name index. The table's own storage order (by order date) is the clustered side of the story; the name index is a separate list whose entries point back to rows that live in a different order — which is exactly what makes it non-clustered.

▶ Watch the 2-minute Short: Clustered vs Non-Clustered Index — What's the Actual Difference?

Want to see indexes earn their keep in a real diagnosis? Our step-by-step tutorial Why Is My SQL Server Slow? watches a query drop from 12,388 page reads to 15 once the right index exists.

Sources

  • Microsoft Learn — Clustered and nonclustered indexes: a clustered index sorts and stores the data rows themselves, so there can be only one per table; a table with no clustered index is a heap; a non-clustered index has a structure separate from the data rows, and its pointer to a row is called a row locator — the clustered index key for a clustered table, a pointer to the row for a heap.
  • Microsoft Learn — Create nonclustered indexes: the maximum number of nonclustered indexes per table is 999.

Disclosure: Visual Studio Learn is reader-supported. Some links may be affiliate links, which means the channel may earn a small commission at no extra cost to you. Only tools actually used in the tutorials are ever linked, and affiliate relationships never change a verdict.

Enjoying these tutorials?

Get new Visual Studio Learn posts by email — free, no spam:

Follow along: YouTube · Facebook · RSS

Comments