windows,linux,cpanel,sql server,classic asp,php,wordpress,facebook,magento,jquery,css,javascript MS SQL Server get next Identity insert value » abhilash

MS SQL Server get next Identity insert value

Image

 

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