Blog

Reset Identity Column Value in SQL Server

Posted by Rohatash Kumar Blogs | SQL Nov 28, 2012
In this blog, You will see how to use DBCC CHECKIDENT in SQl Server.

In this blog, You will see how to use DBCC CHECKIDENT in SQl Server.  First of all we create a table which named [student table]. Table has a column named stu_id with identity property (1,1). The following is the sample data for the [student table] Table:

Image1.jpg

Using  DBCC CHECKIDENT

DBCC CHECKIDENT is used to manually set a new current identity value for the identity column.

Example

The following example show the current identity value in the  sty_id column in the [student table] table to a value of 5. Because the table has existing rows, the next row inserted will use 5 as the value, that is, the new current increment value defined for the column value plus 1.

  use master

  GO

  DBCC CHECKIDENT ([student table], reseed, 5)

  GO

 

Press F5 to execute it.

 

Now to check identity value insert some data into the table.

 

Insert into [student table] values('raj','97','D', 'nnb')

Go

Insert into [student table] values('Herron','57','E', 'kkd')

Go

SELECT  stu_id

      ,[stu_name]

      ,[marks]

      ,[Remarks]

      ,[From]

  FROM [master].[dbo].[student table]

 

Now press F5.

 

Image2.jpg

COMMENT USING
PREMIUM SPONSORS
Hire Mobile & Web Developer on demand. 100% satisfaction. Try for 1 week or Money Back. Local and remote developers available all over USA.
Employers - Post Free Jobs
SPONSORED BY
  • MCN is your source for developing solutions involving websites, mobile apps, cloud-computing, databases, BI, back-end services and processes and client-server applications.
Employers - Post Free Jobs