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.
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
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: