Bug when recreating an empty table (repeated identity value)

Jon123

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.
----<p></p><p><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small"></span></span></span></p><p><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small"></span></span></span></p><p><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small"></span></span></span></p><p></p><p></p><p></p><p></p><p>-- USE DATABASE1</p><p></p><p><span class="post-font-size-small"><span class="post-font-size-small"> </span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small"></span></span></span></p><p><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">IF</span></span></span><span class="post-font-size-small"><span class="post-font-size-small"> </span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">OBJECT_ID</span></span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">(</span></span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">'dbo.X'</span></span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">)</span></span></span><span class="post-font-size-small"><span class="post-font-size-small"> </span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">IS</span></span></span><span class="post-font-size-small"><span class="post-font-size-small"> </span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">NOT</span></span></span><span class="post-font-size-small"><span class="post-font-size-small"> </span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">NULL</span></span></span><span class="post-font-size-small"><span class="post-font-size-small"> </span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">DROP</span></span></span><span class="post-font-size-small"><span class="post-font-size-small"> </span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">TABLE</span></span></span><span class="post-font-size-small"><span class="post-font-size-small"> X</span></span></p><p><span class="post-font-size-small"><span class="post-font-size-small"></span></span></p><p><span class="post-font-size-small"><span class="post-font-size-small"></span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">CREATE</span></span></span><span class="post-font-size-small"><span class="post-font-size-small"> </span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">TABLE</span></span></span><span class="post-font-size-small"><span class="post-font-size-small"> X</span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">(<span class="post-font-size-small"><span class="post-font-size-small"> XId </span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">INT</span></span></span><span class="post-font-size-small"><span class="post-font-size-small"> </span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">NOT</span></span></span><span class="post-font-size-small"><span class="post-font-size-small"> </span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">NULL</span></span></span><span class="post-font-size-small"><span class="post-font-size-small"> </span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">IDENTITY</span></span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">(</span></span></span><span class="post-font-size-small"><span class="post-font-size-small">1</span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">,</span></span></span><span class="post-font-size-small"><span class="post-font-size-small"> 1</span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">), <span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">a </span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">int<span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">)</span></span></span><span class="post-font-size-small"><span class="post-font-size-small"> </span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">ALTER</span></span></span><span class="post-font-size-small"><span class="post-font-size-small"> </span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">TABLE</span></span></span><span class="post-font-size-small"><span class="post-font-size-small"> X </span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">ADD</span></span></span><span class="post-font-size-small"><span class="post-font-size-small"> </span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">CONSTRAINT</span></span></span><span class="post-font-size-small"><span class="post-font-size-small"> PK_X </span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">PRIMARY</span></span></span><span class="post-font-size-small"><span class="post-font-size-small"> </span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">KEY</span></span></span><span class="post-font-size-small"><span class="post-font-size-small"> </span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">CLUSTERED </span></span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">(</span></span></span><span class="post-font-size-small"><span class="post-font-size-small">XId</span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">)</span></span></span><span class="post-font-size-small"><span class="post-font-size-small"> <span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small"><br></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></p><p></p><p><span class="post-font-size-small"></span></p><p><span class="post-font-size-small"><span class="post-font-size-small"></span></span></p><p><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small"></span></span></span></p><p></p><p><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small"></span></span></span></span></p> ----

<span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small"><p></p><p></p><p>-- USE DATABASE2</p><p></p></span><p></p></span><p></p></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small"></span></span></span><p><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">IF</span></span></span><span class="post-font-size-small"><span class="post-font-size-small"> </span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">OBJECT_ID</span></span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">(</span></span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">'dbo.X'</span></span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">)</span></span></span><span class="post-font-size-small"><span class="post-font-size-small"> </span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">IS</span></span></span><span class="post-font-size-small"><span class="post-font-size-small"> </span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">NOT</span></span></span><span class="post-font-size-small"><span class="post-font-size-small"> </span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">NULL</span></span></span><span class="post-font-size-small"><span class="post-font-size-small"> </span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">DROP</span></span></span><span class="post-font-size-small"><span class="post-font-size-small"> </span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">TABLE</span></span></span><span class="post-font-size-small"><span class="post-font-size-small"> X</span></span></p><p><span class="post-font-size-small"><span class="post-font-size-small"></span></span></p><p><span class="post-font-size-small"><span class="post-font-size-small"></span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">CREATE</span></span></span><span class="post-font-size-small"><span class="post-font-size-small"> </span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">TABLE</span></span></span><span class="post-font-size-small"><span class="post-font-size-small"> X</span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">(</span></span></span><span class="post-font-size-small"><span class="post-font-size-small"> a </span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">INT</span></span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">,</span></span></span><span class="post-font-size-small"><span class="post-font-size-small"> </span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">/*different column order to force a rebuild*/</span></span></span></p><p><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small"></span></span></span><span class="post-font-size-small"><span class="post-font-size-small"></span></span></p><p><span class="post-font-size-small"><span class="post-font-size-small">XId </span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">INT</span></span></span><span class="post-font-size-small"><span class="post-font-size-small"> </span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">NOT</span></span></span><span class="post-font-size-small"><span class="post-font-size-small"> </span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">NULL</span></span></span><span class="post-font-size-small"><span class="post-font-size-small"> </span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">IDENTITY</span></span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">(</span></span></span><span class="post-font-size-small"><span class="post-font-size-small">1</span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">,</span></span></span><span class="post-font-size-small"><span class="post-font-size-small"> 1</span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">)<span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">)</span></span></span><span class="post-font-size-small"><span class="post-font-size-small"> </span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">ALTER</span></span></span><span class="post-font-size-small"><span class="post-font-size-small"> </span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">TABLE</span></span></span><span class="post-font-size-small"><span class="post-font-size-small"> X </span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">ADD</span></span></span><span class="post-font-size-small"><span class="post-font-size-small"> </span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">CONSTRAINT</span></span></span><span class="post-font-size-small"><span class="post-font-size-small"> PK_X </span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">PRIMARY</span></span></span><span class="post-font-size-small"><span class="post-font-size-small"> </span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">KEY</span></span></span><span class="post-font-size-small"><span class="post-font-size-small"> </span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">CLUSTERED </span></span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">(</span></span></span><span class="post-font-size-small"><span class="post-font-size-small">XId</span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">)</span></span></span><span class="post-font-size-small"><span class="post-font-size-small"> </span></span></span></span></span></p><p></p><span class="post-font-size-small"><p></p><span class="post-font-size-small"><p></p><p>　</p><p></p></span><p></p></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small"></span></span></span><p><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">INSERT</span></span></span><span class="post-font-size-small"><span class="post-font-size-small"> x</span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">(</span></span></span><span class="post-font-size-small"><span class="post-font-size-small">a</span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">)</span></span></span><span class="post-font-size-small"><span class="post-font-size-small"> </span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">SELECT</span></span></span><span class="post-font-size-small"><span class="post-font-size-small"> 1</span></span></p><p><span class="post-font-size-small"><span class="post-font-size-small"></span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small"></span></span></span></p><p><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">INSERT</span></span></span><span class="post-font-size-small"><span class="post-font-size-small"> x</span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">(</span></span></span><span class="post-font-size-small"><span class="post-font-size-small">a</span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">)</span></span></span><span class="post-font-size-small"><span class="post-font-size-small"> </span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">SELECT</span></span></span><span class="post-font-size-small"><span class="post-font-size-small"> 2</span></span></p><p><span class="post-font-size-small"><span class="post-font-size-small"></span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small"></span></span></span></p><p><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">INSERT</span></span></span><span class="post-font-size-small"><span class="post-font-size-small"> x</span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">(</span></span></span><span class="post-font-size-small"><span class="post-font-size-small">a</span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">)</span></span></span><span class="post-font-size-small"><span class="post-font-size-small"> </span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">SELECT</span></span></span><span class="post-font-size-small"><span class="post-font-size-small"> 3</span></span></p><p><span class="post-font-size-small"><span class="post-font-size-small"></span></span><br></p><p><span class="post-font-size-small"><span class="post-font-size-small"></span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small"></span></span></span></p><p><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">SELECT</span></span></span><span class="post-font-size-small"><span class="post-font-size-small"> </span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">*</span></span></span><span class="post-font-size-small"><span class="post-font-size-small"> </span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">FROM</span></span></span><span class="post-font-size-small"><span class="post-font-size-small"> X </span></span></p><p><span class="post-font-size-small"><span class="post-font-size-small"></span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small"></span></span></span></p><p><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">DELETE</span></span></span><span class="post-font-size-small"><span class="post-font-size-small"> X </span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">/* Bug&nbsp;only manifests if target is empty*/</span></span></span></p></span><p></p></span><p><span class="post-font-size-small"></span></p>

----

<span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small"><p></p><p></p><p></p><p><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small"></span></span></span></p><p><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small"></span></span></span></p><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">/* AFTER DELOY&nbsp; RUN CODE BELOW IN DATABASE2 */</span></span></span><p></p></span></span></span><p><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small"></span></span></span></p><p><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small"></span></span></span><span class="post-font-size-small"><span class="post-font-size-small"> </span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small"></span></span></span></p><p><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">INSERT</span></span></span><span class="post-font-size-small"><span class="post-font-size-small"> x</span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">(</span></span></span><span class="post-font-size-small"><span class="post-font-size-small">a</span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">)</span></span></span><span class="post-font-size-small"><span class="post-font-size-small"> </span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">SELECT</span></span></span><span class="post-font-size-small"><span class="post-font-size-small"> 4</span></span></p><p></p><p><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">SELECT</span></span></span><span class="post-font-size-small"><span class="post-font-size-small"> </span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">*</span></span></span><span class="post-font-size-small"><span class="post-font-size-small"> </span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">FROM</span></span></span><span class="post-font-size-small"><span class="post-font-size-small"> X </span></span><span class="post-font-size-small"><span class="post-font-size-small"><span class="post-font-size-small">---&nbsp;Expected 4,4</span></span></span></p><p></p></span><p></p></span><p></p><p></p><p></p><span class="post-font-size-small"><p></p><span class="post-font-size-small"><p></p><span class="post-font-size-small"><p></p><p>--- Result is	3,	4 </p><p>-- (a clash with previous deleted row 3,3)</p></span><p></p></span><p><span class="post-font-size-small"></span></p></span><p><span class="post-font-size-small"><span class="post-font-size-small"></span></span></p>

Jessica R

Hi @Jon123 ,

Thanks for your post!

I am looking into this, please bear with me.

Jessica R

Hi @Jon123 ,

Thanks for your patience!

Our developers looked into this and the reason for this behavior is a peculiarity in how SQL Server handles DBCC CHECKIDENT RESEED. As documented in https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkident-transact-sql, if the table is empty, the increment is not added on to the reseed value for the first row inserted, but if the table is not empty, the increment *is* added on to the reseed value for the first (new) row inserted.

I'm afraid we haven't been able to work out a way to change this that doesn't break the next identity value in other situations. We originally thought we may be able to put a conditional in Compare to fix this, but we're unfortunately unable to tell the difference between a table that has never had data, and a table that used to have data but is now empty.

With that, I'm really sorry to say that we're not able to address this at the moment unless SQL Server changes the behavior. 

At the moment, you would need to manually edit the script. Changing 

select&nbsp;@idVal&nbsp;=&nbsp;IDENT_CURRENT(N'[dbo].[X]')<br>
to

select @idVal = IDENT_CURRENT(N'[dbo].[X]') + case when exists (select top 1 1 from [dbo].[RG_Recovery_1_X]) then 0 else IDENT_INCR(N'[dbo].[X]') end

appears to work.

I'm so sorry as I know this is not the answer you were hoping for!