Does non-unique clustering key increase likelyhood of page level locks?

Question

I have a table with many columns of which the total max size greatly exceeds the 8k boundary. This table contains a ModuleID column which basically tells u what type of object it is (dont worry - I didnt design this) of which there are maybe 15 different values. And then it has a unique column called propertyID which is also an IDENTITY(1,1) and then incremented by SQL Server. There is a clustered index on ModuleID, this value is always known for selects, for updates propertyID is used (moduleID is rarely in scope here). Table contains several million rows.

Regarding an INSERT my questions are thus:
a) Does the non-unique clustered key increase the likelyhood of SQL Server holding exclusive page-level locks instead of KEY (row) locks?
b) Changing the clustered key to the unique propertyID which is incremented would enable SQL Server to hold exclusive KEY locks instead, and these would always go into the last page of the clustered index?

Would the fact that the table is (in some installations) partitioned on moduleID change your answers?

Answer 1

I don't have any hard fact or numbers to back this up, but from my experience and knowledge, I'd say: no.

Surprised??

Let me explain: the clustering index in SQL Server must be unique. Yes, it must. No, you don't have to make it unique - if you don't, SQL Server itself will take care of that by adding a 4-byte "uniqueifier" (an INT) to your rows that clash in the clustering index.

So in the end, you always have a unique clustering key - you have to, since this is really the "physical" pointer to the actual data - how else would a non-clustered index be able to find that data quickly and efficiently, if not for a unique "pointer" of sorts??

So even if you're a bit careless and chose a column (or set of columns) for your clustering key that aren't guaranteed to be unique, SQL Server will make sure they are, in the end - possibly at the expense of additional storage needed for those uniqueifiers.

So you're really always left with a unique clustering key, and thus, there shouldn't be any difference in the likelihood of page level locks.