Interview Question: Does clustered index store data in physical order?

Well, where to start…. I have had opportuinity to interview dozens of candidates for DBA and developer positions. One of my very first questions is about indexes, clustered index: Does clustered index store data in physical order?

What do you think? Does it? Well… I interviewed only 2 people who knew the correct answer during the last 3 years! The short answer is: *NO! *

Check these articles if you wish details:

  • Paul Randal’s article in English,
  • My article in Hungarian based on Paul’s one. (I asked permission from him in advance to reuse his example!)

Why am I writing this post? I heard an interesting answer from more than one candidate this week and would like to share it.

Candidate said:

If you insert a row into the middle of a table which has a clustered index, then the whole table must be restructured, all rows must be reorganized physically.

WTF? Does it mean a 100 GB big table must be reorganized? Candidate said: yes. Well, you can imagine my feedback about the candidate :-).

Another thing I would like to highlight: when you use a search engine for searching SQL Server interview questions, make sure you got answers from a trustworthy source! I do not want to name that SQL Server MVP who spreads this myth. I may write an email to him instead.

Add comment