We want to have our client apps to be able to generate identifiers for objects they create.
We have 2 options to implement that:
- Have GUID (generated by client) as PK, non-clustered and at the same time have AUTO_INCREMENT int column with clustered index on it for later, under the hood usage mainly in JOINS
- Abandon AUTO_INCREMENT column completely
We are not worried about storage space.
Is there any significant gain going with option 1, or in other words, anything problematic with option 2?
>Solution :
For your requirement:
We want to have our client apps to be able to generate identifiers for objects they create.
I would suggest you have a regular int/bigint identity column primary key as this typically performs better than a uniqueidentifier.
And then add a surrogate GUID (uniqueidentifier) column, which would be indexed as a unique, non-clustered index.
This gives you the best of both worlds. A well performing primary key, and the ability to assign identifiers to your objects client side.