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.
