Pages

Saturday, December 20, 2014

Easy way to reseed identity column ? TIP #77

 

Sometimes, we may require to reseed identity column of a table due to various reason.

For example we have deleted a record from a Student which have an identity column StudentId.  Below is schema

StudentSchema

Now it has 3 rows as shown below

StudentRows

Now suppose we have deleted record 3 which is studentID 3.

Now when we want to insert a new record StudentId start with 4 and we want it should be 3.

So how to check and reseed it ?

To achieve this task SQL SERVER provided DBCC CHECKIDENT facility.

DBCC CHECKIDENT has 2 commands

1) To Check current Identity value.

DBCC CHECKIDENT (TABLENAME, NORESEED)

2) To reseed an identity column we have following command

DBCC CHECKIDENT ( TABLENAME , RESEED, VALUE) 

Lets check student table identity to check current column value and current Identity value we will write following command

DBCC CHECKIDENT(tblStudentSource,NORESEED)

See below snap when after running this command

Identity_Check

Now if you see 3 is current identity and column value 2 it means if I Insert a new row in tblStudentSource then that row will have studentId = 4 as a next identity column

Now I need to reseed it to 3 means if Insert a new record then studentId should be 3 to do this I have to reseed tblStudentSource.

DBCC CHECKIDENT(tblStudentSource,RESEED,2)

StudentReseed

Now to cross check whether it is reseed or not I write NORESEED command again as get following result

Check_Ressed

It means identity value 2 and current column value is 2 means if now I insert a new record we will get studentId = 3 as a identity column this what we want actually.

So. We did NORESSED, RESSED an identity column.

I hope it may help you somewhere in your work.

Thanks

Rj

No comments:

Post a Comment