Bug when recreating an empty table (repeated identity value)
Scripts below can be used to recreate the problem. This concerns an SQL Compare deploy from DATABASE1 to DATABASE2. I am forcing a rebuild of table X by having different column order. There is a row with (IDENTITY) Id 3, but all rows are deleted before deploy. After deploy the next inserted value will also be 3.The reason this happens is because DBCC CHECKIDENT(N'[dbo].[x]', RESEED, (3)) in the deploy script works differently if no values have been inserted in the recreated table. If the table has not been inserted to this value need to be increased by the IDENTITY increment.
-- USE DATABASE1 IF OBJECT_ID('dbo.X') IS NOT NULL DROP TABLE XCREATE TABLE X( XId INT NOT NULL IDENTITY(1, 1), a int) ALTER TABLE X ADD CONSTRAINT PK_X PRIMARY KEY CLUSTERED (XId)----
-- USE DATABASE2IF OBJECT_ID('dbo.X') IS NOT NULL DROP TABLE XCREATE TABLE X( a INT, /*different column order to force a rebuild*/XId INT NOT NULL IDENTITY(1, 1)) ALTER TABLE X ADD CONSTRAINT PK_X PRIMARY KEY CLUSTERED (XId) INSERT x(a) SELECT 1INSERT x(a) SELECT 2INSERT x(a) SELECT 3
SELECT * FROM X DELETE X /* Bug only manifests if target is empty*/
/* AFTER DELOY RUN CODE BELOW IN DATABASE2 */ INSERT x(a) SELECT 4SELECT * FROM X --- Expected 4,4