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.
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.