2

I'm focusing on SQL Server here, but I think this is a database agnostic question, really.

I have a table whereby a Guid is logically the primary key. However, uniqueidentifiers apparently make terrible clustered indexes, so I'm planning on creating a dummy key as an identity column and making that the clustered index for the table.

When it comes to foreign keys referencing this table however, should the foreign keys reference the clustered index or the primary key? I'm thinking it should be the primary key still, right? And that's because the clustered index doesn't technically HAVE to be unique, whereas the primary key does...?

Also, just to double check, if I make another column the clustered index, that doesn't mean I need to add a non-clustered index to my primary key column, does it? In other words, does my column automatically have an index on it just by being the primary key?

  • Why do you need a GUID and an IDENTITY column? Can you not just use an IDENTITY column as a UNIQUE CLUSTERED INDEX? – mheptinstall Oct 22 '14 at 20:53
  • Ideally I would use the GUID column as the clustered primary key for the table, but this can apparently lead to performance problems (unless newsequentialid() is used, but I can't use this function in Azure). The suggestions online all seem to be to create a dummy column and make that the clustered index for the table. Making it an identity column as well would guarantee uniqueness (identity & auto-incrementing columns are the same thing...right?) – dark_perfect Oct 22 '14 at 21:08
2

Taken from the MSDN article on FOREIGN KEY Constraints

A FOREIGN KEY constraint does not have to be linked only to a PRIMARY KEY constraint in another table; it can also be defined to reference the columns of a UNIQUE constraint in another table.

I think it is perfectly fine to define a UNIQUE CLUSTERED INDEX on an IDENTITY column and define the PRIMARY KEY on different columns, often with a NONCLUSTERED INDEX but you don't have to specify an index.

  • Hmm...does this mean in my scenario above the foreign key is better suited against the clustered index or primary key? Presumably if the foreign key references the primary key, an additional lookup to the clustered index is required, which means putting it against the clustered index would yield better performance...no? If the foreign key references the clustered index, it can go straight to the row? – dark_perfect Oct 22 '14 at 20:58
  • 3
    I would reference the IDENTITY column rather than GUID. If you put it against the GUID the CLUSTERED INDEX will need to be accessed anyway to retrieve any additional columns from your table. – mheptinstall Oct 22 '14 at 21:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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