DELETE, TRUNCATE and RESEED Identity IN SQL SERVER

TRUNCATE ACTION IN SQL

1.select * from test_table

sno	name
1	chandra
2	vipin
3	raghu
4	taslim
5	ravi
6	manju
7	neha
8	neeraj
9	mohit
10	ashish

2.TRUNCATE table test_table

Sno    name

No data in table.

After truncate table always row start in table new sno.

3. insert into test_table values ('chandra')

insert into test_table values ('ravi')

insert into test_table values ('vipin')

insert into test_table values ('neha')

sno	name
1	chandra
2	chandra
3	ravi
4	vipin
5	neha

DELETE ACTION IN SQL

4.delete from test_table where sno=4

sno	name
1	chandra
2	chandra
3	ravi
5	neha

After delete new row data start from deleted sno (5)+1 = 6

5. insert into test_table values ('vipin')

sno	name
6	vipin
2	chandra
3	ravi
5	neha

RESEED ACTION IN SQL

6.DBCC CHECKIDENT ('test_table', RESEED, 1)

When run this query in SQL we have one message from SQL side:

Checking identity information: current identity value '6', current column value '1'.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

This new value logic is Reseed Value + Interval Value – in this case it will be 1+1 = 2.

sno      name

6          vipin

2          chandra

3          ravi

2          neha (new value insert in table)

5          neha

3          rajesh (new value insert into table)

7.DBCC CHECKIDENT ('test_table', RESEED, 10)

Checking identity information: current identity value '3', current column value '10'.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

8.insert into test_table values ('reseed data')

sno	name
6	vipin
2	chandra
3	ravi
2	neha
5	neha
3	rajesh
11	reseed data

New Row insert with (10+1=11 sno becoues our cuurent value is 10)

9.delete from test_table where sno=11

sno	name
6	vipin
2	chandra
3	ravi
2	neha
5	neha
3	rajesh
12	reseed data

We have delete sno 11 and then insert new data then start sno 12.

 10.DBCC CHECKIDENT ('test_table', RESEED, 1000)

sno      name

6          vipin

2          chandra

3          ravi

2          neha

5          neha

3          rajesh

12        reseed data

1001    cp