0

We are in an environment where, for several scenarios, it is required that rows in our database(s) have a GUID identifier (generated in app, not by the DB. The reason for this is because we are in a distributed/micro service environment).

Currently the GUID is a primary key.

These tables also have another integer (identity) identifier, which is unique and set as the clustered index. This is required as the default for SQL server is to have the PK as the clustered index, which is bad practice for GUIDs.

Given that there will always be a GUID, and there will always be a unique integer identifier, is there any disadvantage of having the integer identity column as the PK, and the GUID as a unique column?

Current:

  • GUID (PK)
  • INT (Unique, Clustered)

Proposed:

  • INT (PK, Clustered Index (by default))
  • GUID (Unique)

I've read up a bit on using GUIDs and what you need to be careful about. To speak generally, there seems to be disadvantages of the "Current" (GUID PK, INT unqiue/clustered). To my knowledge these would include:

  • Performance
  • Foreign keys/joins on GUIDS
  • Large composite keys
  • Size (tables with many FKs now have many GUID columns)
  • Having to work with GUIDS at the SQL level (I.E. Debugging)
  • Non standard*

What I mean by non-standard is that there is more to understand for developers and certain things you have to do/change when using a GUID approach to ensure it works well.

Again, to my knowledge, you don't get the above disadvantages when using the the "Proposed" (INT PK, GUID unique) approach, and I am surprised that this is not a more readily proposed solution, which leads me to think that there may be something that I am missing.

Given the requirements (there will always be two ways to unique identify a row, GUID and INT), is there any reason why you would not have the INT as the primary key?

wdavo
  • 5,070
  • 1
  • 19
  • 20
  • How do the ints get uniquely assigned if the purpose of the GUIDs is to allow you to work in a distributed fashion? – Damien_The_Unbeliever Mar 14 '18 at 08:15
  • The ints are generated by the DB automatically. They will never be used outside of the ‘local’ system (E.G queries within the same DB). GUIDs are still required in any distributed context. – wdavo Mar 14 '18 at 08:26
  • A quick web-search will give you all the information you need on this. – MJH Mar 14 '18 at 08:44
  • 1
    This statement, "This is required as the default for SQL server is to have the PK as the clustered index" is just wrong. It is not required. It is, however, driven by inexperience and laziness by the database designer. You have a single clustered index - choose it carefully. And no - we don't generalize the decision process because it is and should be unique to every table. [More here](https://stackoverflow.com/questions/2267326/how-to-choose-the-clustered-index-in-sql-server) – SMor Mar 14 '18 at 12:18
  • I don’t see how the statement is wrong. Required may have been too strong of a word (it’s not technically required), how about highly recommended? If the default is to use the PK (unless a clustered index is specified), which it is, and the PK is a GUID, that should not be the clustered index. I agree with your point that I shouldn’t be approaching this in a general manner, and maybe an int PK isn’t always the best choice for the clustered index, but it seems like a better starting point. The link you provided agrees. I didn’t mention in the Q that the int PK was an IDENTITY column, updated. – wdavo Mar 14 '18 at 13:07

0 Answers0