Reseeding IDENTITY Seed Value of Column with PK; Beware

Beware while reseed with DBCC Checkident('Tab_Name',Reseed,1) because, the moment identity value reaches to the values which is present in ur identity column, it won’t allow you to enter record due to duplicity, since u said ur id column is PK and throw error...

For explanation we are taking creating a table, this table has no Identity property set for ID field

--TABLE WITHOUT iDENTTITY

create table tab_test

(id int primary key,

name varchar(10))

Command(s) completed successfully.

Now lets insert a record with Id value 2

insert into tab_test values(2,'vikram')

(1 row(s) affected)

Lets set the identity property if ID column with the help of designer.

Go the Table Designer à Select ID column à Go to Property à Expand Identity specification àset YES to isIdentity àset seed and increment value to 1.

Now since our identity value is set lets find the current identity value

select ident_current('tab_test') as CurrentIdentity

CurrentIdentity

---------------------------------------

2

So now if we try to insert record, it will take 3 as ID value. Lets insert a record in the table

insert into tab_test values('keshari') --IT Will take identity value as 3

select * from tab_test

id          name

----------- ----------

2           vikram

3           keshari

Now lets reseed the identity value to 0

dbcc checkident('tab_test',reseed,0)

Checking current identity value

select ident_current('tab_test') as CurrentIdentity

CurrentIdentity

---------------------------------------

0

Now lets insert record in the table and see the ID field value

insert into tab_test values('mahapatra') --It will take identity values as 1

select * from tab_test

id          name

----------- ----------

1           mahapatra

2           vikram

3           keshari

Now here we saw the new id value was taken as 1, lets find what is the current and next identity value

select ident_current('tab_test') as CurrentIdentity

CurrentIdentity

---------------------------------------

1

Now we can see the current value is 1, so the next identity value will be 2, but record with 2 as ID value is already exist so will it take 2 as next identity value?

insert into tab_test values('Sibani') --now since the identity current value is 2 and Id column with 2 value is present, it wont allow

Msg 2627, Level 14, State 1, Line 1

Violation of PRIMARY KEY constraint 'PK__tab_test__3213E83FE45A5CA7'. Cannot insert duplicate key in object 'dbo.tab_test'. The duplicate key value is (2).

The statement has been terminated.

Conclusion: While Reseed value for identity column having primary Key defined on it, be aware else you will get error in some point of time if the next identity value exist in the table records.

Ask yourself, are you having fun with SQL Server?

IDENT_CURRENT function vs @@IDENTITY in SQL Server

IDENT_CURRENT function returns the last identity value generated for a specified table or view. The last identity value generated can be for any session and any scope.

Let’s take an example for learning purpose

create table EmpTemp

(id int identity(1,1),

empName varchar(10))

Command(s) completed successfully.

Here ID is an identity column which will gets increment by one with every insert since the seed for the increment is set to 1.

Let’s insert few rows to EmpTemp table. Values for ID column is not required since it is an identity column, so automatically on insertion of any row to the table set the value of ID in corresponding row.

insert into EmpTemp(empName) values ('Aakhya')

insert into EmpTemp(empName) values ('Kruthika')

insert into EmpTemp(empName) values ('Aanandita')

insert into EmpTemp(empName) values ('Chandanika')

insert into EmpTemp(empName) values ('Ceitra')

insert into EmpTemp(empName) values ('Arasi')

Accede to see the output of Select query

select * from EmpTemp

id          empName

----------- ----------

1           Aakhya

2           Kruthika

3           Aanandita

4           Chandanika

5           Ceitra

6           Arasi

(6 row(s) affected)

Now run the below query to see the result of IDENT_CURRENT. The IDENT_CURRENT function takes only Table or View name as parameter.

select IDENT_CURRENT('EmpTemp')

---------------------------------------

6

(1 row(s) affected

Now insert one more row and then check this:

insert into EmpTemp(empName) values ('Siddhiksha')

(1 row(s) affected)

select IDENT_CURRENT('EmpTemp')

--------------------------------------

7

(1 row(s) affected)

What does the function do ?

CONCLUSION: This always gives you the current id value from the table.

PART 2: Interesting to see!!! THE @IDENTITY

Run the query below.

insert into EmpTemp(empName) values ('Sakshi')

(1 row(s) affected)

select IDENT_CURRENT('EmpTemp')

select @@IDENTITY

OUTPUT

---------------------------------------

8

Both IDENT_CURRENT and @@IDENTITY that means @@IDENTITY does the same thing!!!!

Hang around and observe:

Open a new Query window and run the below query again:

select IDENT_CURRENT('EmpTemp')

---------------------------------------

8

(1 row(s) affected)

select @@IDENTITY

---------------------------------------

NULL

(1 row(s) affected)

Weird and wonderful! correct ???

·       IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.

@@IDENTITY returns the last identity value generated for any table in the current session, across all scopes.

Post Reference: Vikram Aristocratic Elfin Share