Indentity Column reseed

Identity column in SQL server

______________________________

Issue: Some just came to me and said, Pavel I guess you have inserted duplicate columns on table. Can you remove them? But, I have identity columns of that table can you make sure you don’t break that.

Solution:

I delete the record by using this:  select * from Premium where id = 3034

It definitely breaks the identity. Now, I have to fix the table. 

Let’s check the damage.

DBCC CHECKIDENT (Premium)

Result:

Checking identity information: current identity value '3034', current column value '3034'. DBCC execution completed. If DBCC printed error messages, contact your system administrator.

This means I have to reseed the table to apply 3033. Because 3034 has been deleted. So, let me run this

DBCC CHECKIDENT (Premium, reseed, 3033)

Result:

Checking identity information: current identity value '3034', current column value '3033'.DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Verify

DBCC CHECKIDENT (Premium)

Checking identity information: current identity value '3033', current column value '3033'. DBCC execution completed. If DBCC printed error messages, contact your system administrator.