About two years ago, the company I was working for laid off a bunch of people, including me. While losing my job sucked, this particular layoff was especially painful for a couple of reasons – 1) at this company, I got to work with a good friend of mine from high school and college and 2) the company made video games. If you’ve ever worked for a video game company, you know what I am talking about. If you never have, well… All I can say is, try getting hired at one. You won’t regret it.

So I was unceremoniously thrown out into the job hunting world and started going on  interviews. After a couple, I started to notice a pattern to the questions I was being asked. In fact, one question always was asked: How many clustered indexes can a table have?

Now, maybe it’s just me. Maybe for some reason, the answer to this question stuck in my head the first time I heard it and for most people, it doesn’t. The answer is: There can be only one.

The clustered index defines how the data pages in the table are ordered on disk. A brief overview is here (this was written for SQL 2000, but the concept still applies). A more detailed one is here. You can only physically order the data in one way on the disk. Therefore, there can only be one clustered index.

During this forced job hunting period, I was interviewing for a company and was meeting with two people simultaneously, getting peppered with questions from both at once. One of them asked the clustered index question. After I answered, I said “You know, I have to ask.. I’ve gotten asked this question on every single interview I’ve been on and it seems like a very basic concept to me. Do you really get a lot of people who don’t know the answer?” The two guys looked at each other, chuckled, and said “No comment.”

Stand out from the crowd. Learn this answer.