Best practices for generating new identifiers for persistent objects

identitysql

In a typical scenario, an object that needs to be persisted in the database will be created in application code without an identifier, and later saved to a table in the database. Some database construct, such as an identity column, will generate a new identifier, typically an integer that's unique for all of the rows of that table. This identifier can then be returned to application code and then treated as a key.

CREATE TABLE Users (
    UserId int IDENTITY(1,1) NOT NULL, DisplayName nvarchar(max), 
    UserName nvarchar(50), [Password] nvarchar(50))
GO

INSERT INTO Users (DisplayName, UserName, [Password]) 
    VALUES ('Joe Shmo', 'jshmo', 'pw1')
SELECT @@IDENTITY

Another common scenario is for the identifier to be GUID:

CREATE TABLE Users (
    UserId uniqueidentifier NOT NULL, DisplayName nvarchar(max), 
    UserName nvarchar(50), [Password] nvarchar(50))
GO

DECLARE @id uniqueidentifier = NEWID()
INSERT INTO Users (UserId, DisplayName, UserName, [Password]) 
    VALUES (@id, 'Joe Shmo', 'jshmo', 'pw1')
SELECT @id

This second way provides the following advantage: the GUID can be generated in application code, which can then be used as a key for application caching/referencing purposes before the object is ever saved. However, there is a cost associated with using GUIDs in size and, especially look-up costs. These may be trivial when the table is small, but can become a major factor when the table is massive.

Are there any best practices, or techniques for the cases where an identifier unique to the object type needs to be generated in application code, but cannot be a GUID for whatever reason?

One thought was to have a another table generating identifier wrapped by an identifier service which application code could call.

Best Answer

both approaches are valid

if only one database server generates the keys, a sequence ID is fine, and is (as you noted) somewhat more efficient (and a heck of a lot easier to read) than a GUID

if multiple servers generate the keys then go with a GUID

another option is to let the database generate a sequence key for internal relationships, and use a GUID as a unique secondary key

as usual, it depends on how you create and use the data

Related Topic