I'd like to ask two supplementary/follow-on questions, further to this previous/existing question: Is there any tangible difference between a unique clustered index and a clustered primary key?

  • That question starts with, "I understand that there may be a difference in meaning or intent between the two". I'm a programmer not a DBA, and this fundamental might be unclear to me: what is the difference in meaning or intent between the two?

  • My summary of the accepted answer, i.e. its most important statement IMO, is that it says, "I don't think there's any difference". If that's so then why did Microsoft implement "clustered indexes"? Why not just say instead, "It's always clustered on the primary key, and you should define as the primary key whatever you want it to be clustered on"?

It seems to me that a primary key (already) is a unique clustered index.


Furthermore, here's a specific problem by way of example.

Let's say I have a table of Users (with a userId as its primary key), and a second table (e.g. Items) which defines items owned by each user. A user can own many items; each item is owned by one user, and has an itemId.

So the itemId could be the primary key of the Items table; and each row in the Items table (which has an itemId) also has a userId to identify its owner.

That's a good way to define a 1-N relationship, isn't it? Assume a foreign key contraint on userId, with Users being the parent table.

At run-time I usually want to retrieve all the items owned by a user, therefore the Items table should be clustered on its userId column.

[Users]
  userId
  + plus other user-specific fields

[Items]
  userId
  itemId
  + plus other item-specific fields

I think there are two ways to define this Items table:

  • itemId is primary non-clustered key, and (userId,itemId) is unique clustered index

... or:

  • (userId,itemId) is primary clustered key, and itemId is unique non-clustered index.

Which of the above two is better or more correct, semantically and/or practically, and why?


In case it makes a difference, the itemID is an artificial key: its purpose is to disambiguate/identify the item (and/or identify the item, within the set of items owned by the user).

  • 'Physically' the itemID is probably globally unique (or unique within the table, anyway): because databases make it easy to create a globally-unique artificial key.

  • 'Logically' I wouldn't mind if it were not-globally-unique, but were instead only unique-within-each-user, such that I needed both userId and itemId to uniquely identify an item, i.e.

    (userId,itemId) is primary clustered key and itemId is unique non-clustered index.

So I think it isn't altogether wrong to see (userId,itemId) as a composite primary key?

Apparently it's fine and normal to use "two separate attributes" as the primary key of an associative table when it's an N-to-N relationship. Is it wrong (e.g. harmful in some way, for some reason) to use two attributes as the primary key of an object in a 1-to-N relationship? Is it wrong to say that the owner ID is part of the object's identity?

  • 2
    It might help if you don't mix up logical and physical concepts. Constraints, including primary key and unique constraint, are logical concepts that should be defined by your data. itemId clearly is the identifying attribute, so it is the primary key, whether you want it or not. Now, whether you want data to be clustered along that attribute (physical concept) is a different question -- looks like clustering along userId, as you say, might be a better approach. – mustaccio Sep 12 '16 at 20:20
  • @mustaccio Isn't it equally true to say that (userId,itemId) is 'the' identifying attribute and so the primary key? – ChrisW Sep 12 '16 at 20:23
  • Don't think of a primary key as always being clustered. This is coincidental due to the way the table designer works. A PK can be clustered. A clustered index can be on a PK. They are, however, not dependent on each other. – Randolph West Sep 12 '16 at 22:30
  • @ChrisW (userId,itemId) are two separate attributes, obviously, and only one of them is identifying -- the item ID, by your own definition. The other one identifies a user. – mustaccio Sep 12 '16 at 22:54
  • @mustaccio Apparently it's fine and normal to use "two separate attributes" as the primary key of an associative table when it's an N-to-N relationship. Is it wrong (e.g. harmful in some way, for some reason) to use two attributes as the primary key of an object in a 1-to-N relationship? Is it wrong to say that the owner ID is part of the object's identity? – ChrisW Sep 12 '16 at 23:44

why did Microsoft implement "clustered indexes"? Why not just say instead, "It's always clustered on the primary key, and you should define as the primary key whatever you want it to be clustered on"?

To answer this question: The primary key on the table does not have to be the Clustered Index Key. A Clustered Index is the place where SQL Server stores the data of the entire table. The Index itself has what are called "key values." Key Values (1 to many) define how the index is ordered, along with statistics and other useful things.

In fact, often it's useful to have a Primary Key that is not the Clustered Index Key. For example, some applications create their own keys or GUIDs in order to relate different tables through foreign keys. Using a GUID as a Clustered Index Key is a bad idea though, since GUIDs don't always have an order, unless you create them sequentially. To solve this, you could create a Clustered Index on an identity column, and place the Primary Key on the GUID column.

Your question has many more questions. I think we need to break this down into many questions, since you're asking about a lot of index concepts.

  • In your example, why not instead create the clustered primary key on the identity column, and a unique index on the GUID column? – ChrisW Sep 12 '16 at 20:07
  • No particular reason. To follow a relational model, I'd guess. SQL Server gives you a lot of freedom to decide these things, yet often they work the same way behind the scenes. – Arthur D Sep 12 '16 at 20:11

I'm going to take a stab at answering this, please correct me if I'm wrong.

There's little or no 'real' (tangible) difference between the two. The only real difference is that a UNIQUE CLUSTERED INDEX can contain NUL values whereas a PRIMARY KEY cannot.

The ability to define them separately (i.e. to define clustering or something other than the primary key) was probably implemented so that you can do your design in two independent stages:

  1. Logical design where you choose the primary/identity key (artificial or otherwise)
  2. Physical design where you optimize for performance (by choosing indexes)

Default behaviour (if not otherwise specified) is to cluster on the primary key; but there's no need to let that physical design choice define the choice of primary key (because you can define a clustered index that's not the default key).

As suggested in @ArthurD's comment there may be two ways where either way might arguably be used, with no clear-cut preference for one or the other.


Edited to add:

There's a specific, theoretical definition of what a 'primary key' is: see What does this definition of a Primary key mean?

So for example if itemId were unique then (userId,itemId) would not (should not or could not) be a primary key: so itemId would be the primary key, and the clustering that you want on (userId,itemId) would be on a non-primary-key index.

Conversely if itemId were only unique within each userId, then obviously `itemd couldn't by itself be a primary key.

  • I'd your answer can be considered correct from function perspective (within SQL Server domain). BUT, you are wrong in understanding the concept of "index" and "key". To me, it is like you compare apple with orange. They are really different even if both are sweet and juicy. – jyao Sep 13 '16 at 23:51
  • Strictly speaking, a SQL Server unique index can contain a NULL value. It goes against the SQL spec by having unique indices disallow more than one instance of a NULL value. You can use filtered indices to simulate ANSI behavior, though. – Jon of All Trades Aug 28 at 21:57

Unique clustered index : when created, it creates an index not any other key and also enforce the uniqueness for the key columns used. this is one way of keeping non-primary key columns unique and have the table shorted physically according to the key columns used.

Clustered primary key : When created, it creates primary key & index both and used when table is needed to be shorted physically in same order as primary key column. Because by definition primary key is unique, it can be clustered or non-clustered without loosing its uniqueness

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.