Aso Covers:
- Reset identity seed after deleting records in SQL Server
- SQL SERVER – DBCC command to RESEED Table Identity Value – Reset Table Identity
- Changing the identity increment value
- How can I increment an identity column without inserting a value?
Here i am going to explain everything from A to Z
Step 1: Create Table
create table tab_t1(
id int identity,
name varchar(20)
)
Step 2: Insert some value
insert into tab_t1 (name) values('rohan')
select * from tab_t1
1 raj
2 raja
3 rohan
Step 3: Check Identity
DBCC checkident ('tab_t1')
Checking identity information: current identity value '3', current column value '3'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC checkident ('tab_t1', reseed)
Checking identity information: current identity value '3', current column value '3'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Step 4: Change Last Identity Value
DBCC checkident ('tab_t1', reseed, 200)
Checking identity information: current identity value '3'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
insert into tab_t1 (name) values('rohan')
select * from tab_t1
1 raj
2 raja
3 rohan
201 rohan