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.