Using SQL db to create object ID or application?
I have an application where my records in my DB need to have unique IDs. Should I leave this responsibility to the SQL db to create them or should this be handled on the application side?
If the former, if I insert a new record input the db and it auto-creates its ID, is the best practice to use output SQL command to read that ID value from the DB and attached it to my object in memory?
If the latter, are there any best practice patterns to handle IDs that you recommend?
Thanks in advance!
14 Replies
Leave it to the db, yeah, just a simple auto-increment PK column will do
If u wanna do it on the application side (which has the benefit of easier scaling) you can use guids.
Just call
Guid.NewGuid() to get a unique idOr you can let the database handle it and also have Guid.
Let the database handle creating guids, if you create them you can get some issues. ( of course letting the server handle it has it’s own set of issues )
Consider not using guids as primary key in case you want to sort by insertion order. Can suggest adding guid ID as a surrogate key.
Thanks for the responses all. So when I insert a new record into the DB and it will create the ID, I need to read that ID back to memory so I can attached it to my object
Id property. Is it ok to use the "output" SQL command when I use insert to return this value each time, or is there a better practice?That is absolutely a good idea. I think EF Core does the same.
Aren't UNIQUEIDENTIFIER generated Guids sorted?
With NEWSEQUENTIALID(), sure. Good point.
I have a ref to this in my notes: https://www.mssqltips.com/sqlservertip/1600/auto-generated-sql-server-keys-with-the-uniqueidentifier-or-identity/
Auto generated SQL Server keys with the uniqueidentifier or IDENTITY
I am designing a table and I have decided to create an auto-generated primary key value as opposed to creating my own scheme or using natural keys. I see that SQL Server offers globally unique identifiers (GUIDs) as well as identities to create these valu
Not sure how valid the post is today 😊, but I generally follow the guideline.
Something that's certain is that Guids generated client side shouldn't be used as a PK, otherwise happy defragmenting!
Same applies to SQL's
NEWID()Yup
Yesnt