Hey there everyone - I know people are feeling like was trying to trick them in the question but I guess the "always" part is what I thought would have been the dead give away. It's really rather simple actually - a Primary Key is NOT always enforced by a clustered index. I think if you read it this way it's simple. As stated by others, the obvious case is when you actually state NONCLUSTERED in the index definition but the "tricky" one is when a clustered index always exists. This is the one that I think a lot of people forget about... But, there are a bunch of combinations that are actually possible and Hugo's answer is very complete. Be sure to review that.

The end result is that there are a lot of things that people think "always" happen in SQL Server and when we become complacent - we get caught (myself included - that's not meant negatively... we all have to be careful!!). I've seen many cases where people just expect that their PKs are always enforced by a clustered and most are...but, then they have problems and there's that one (and then I hear, oh, yeah, I forgot I.........).

Even more so I see a lot of confusion around these three things: the PK, the clustered index and an identity column. There are a lot of assumptions that those are the same thing - even some that think they are inseparable. It's really important to realize that they do not have the be the same thing. They are 3 completely different things (yes, the often come together) but they really are controllable at any level. And, sometimes they should be. It's really more about making the *right* choices - for YOUR workloads. Understanding the internals to make informed decisions about your table design so that you get the best performance in Microsoft SQL Server. What I do in MSSQL is not going to be the same thing I'd do in other environments. This is what's tricky. This is why we're all here. 😉

It's *very* hard in SQL Server to ever say "always" or "never"... except for turning on autoshrink. *NEVER* do that!!! 😉

Cheers,

Kimberly