The problem behind SQL Server Identity field lies where when somebody delete intermediate rows, next identity value may be different from what we expect
for eg. if we are inserting from 1 -50 on a table say table1 with an identity field, and we deleted 40-50 values after insertion
what will be next identity insert value?
Demo
First Create table
create table table1
(
f1 int identity(1,1),
f2 int
)
Now insert 15 rows to this table
insert into table1 select 1
insert into table1 select MAX(f2)+1 from table1 — Execute 14 times
Check maximum value of f1
select max(f1) from table1
Result will be 15
Now delete all rows with f1 greater than 10
delete table1 where f1>10
Again insert 1 row to this table
insert into table1 select MAX(f2)+1 from table1
Now guess what will be the next f1 value
select max(f1) from table1
this will be 16, not 10
This is because of indentity field, to find next identity field
to find next identity field max is not the right way , for this value use IDENT_CURRENT(‘<table name>’)
Syntax Eg.
SELECT IDENT_CURRENT(‘table1′)
this will return 16
Comments are closed.