Wednesday, July 18, 2012

Anti-pattern: identity insert

Something that I often see when a new table that holds reference data is added to a database is something like:
CREATE TABLE [myApp].[Status] (
    [StatusId] INT IDENTITY PRIMARY KEY CLUSTERED ,
    [StatusName] NVARCHAR(20) NOT NULL
)
GO
SET IDENTITY_INSERT [myApp].[Status] ON
GO
INSERT INTO [myApp].[Status] ([StatusId], [StatusName])
VALUES 
    (1, 'Submitted'),
    (2, 'Accepted'),
    (3, 'Denied')
SET IDENTITY_INSERT [Status] OFF
GO
The typical justification that I hear is that the ID values need to be the same across environments (i.e. development, QA, production). I say that if that's the case, don't define the table with an identity column. Identity columns are for when you don't care what value that column takes for a given row. So, if every insert to a given table looks like the above, consider dropping the identity property from the column and save yourself the hassle of dealing with IDENTITY_INSERT.

2 comments:

  1. Agreed. But as you suggest we cannot drop the identity property. It involves multiple steps including adding another column and later renaming it after populating it.

    ReplyDelete
    Replies
    1. You're completely right. When I say "drop the identity property", I mean "drop it from the original definition of the table". That is, don't just blindly make every ID column an identity. Think about if it fits this pattern and, if it does, leave the identity property off.

      Delete