MS SQL Server get next Identity insert value



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?


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.


this will return 16


abhilash, is a programmer by profession and a technology addict with a passion towards browsing , shopping and imaging . My views may be biased on reviews. all images and videos are copyrighted to respective owners.

Add comment

Your Header Sidebar area is currently empty. Hurry up and add some widgets.