Why table can have only one clustered index? No it is not what you think.
Recently, I have been on an interview spree to get a DBA on board in our team. And this was one of the question I asked to nearly everyone. No, not only because this is my favorite question but also because it is a one of the litmus test that help me estimate how clear a DBA is about the core concepts of RDBMS. Problem How many clustered indexes can table have and why? Or. Why a table can have only one clustered index? Below is the most common answer I got for these question: “Because we can have only one primary key on the table”. Well, that answer is a big NO. Yes, NO THAT IS NOT THE CORRECT ANSWER. Surprised, well I was too when I use to hear that answer. Yes, it is correct we can’t have more than one primary key on a table but that is due to its definition and functional requirement. And that’s why by default SQL Server provides functionality of creating a clustered index along with it. My obvious next question to them was “Can we create a primary key without a cluster
