.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month

Home >> Forum >> Windows Application >> Post New QuestionBookmark and Share Subscribe to Forum

identity increment

Posted By: Jayakumar     Posted Date: February 28, 2010    Points:2   Category :Windows Application
Reset the Identity Increment

I have a table with a bigint type column (field) that has an identity seed
of 1 and an identity increment of 1. The column is the primary key for the

After I backup and clean out the database (delete all of the data in the DB)
I need to have the column with the identiy seed/increment value reset to 1
automatically. (start counting at 1 again). How does one do that, because
as it is now, the DB keeps increasing the value of the column from where it
left off, regardless of the fact that I deleted all of the data in the

The DB is MS SQL Server 2000.

Thanks and appreciate any help

Author: Praveen             
Posted Date: March 01, 2010     Points: 5   

Hi Jayakumar,

try this SQL Query to increment to SQL Server Table identity seed

DBCC CHECKIDENT('<tablename>', RESEED, 1)

Author: Jayakumar             
Posted Date: March 01, 2010     Points: 5   



this is ok at my place.

when my client delete all d records, i have generate identity from 1.

how it can be done.

suggest me

Author: Syed Shakeer Hussain             
Posted Date: March 01, 2010     Points: 5   

Hi jayaKumar,

without using Identity and primarykey there is a Query to generate identity from 1.

eg: A table has 'id' column containing null values.

use the Query as :
Select Max(IsNull(id ,0)+1) from tablename store the Max value in a variable and use in insert command.

when you run the query the output will be


when you run the query again the output will be


2)when you try to delete ,alll data will be deleted.

when you run the below query again it will starts from 1.
Select Max(IsNull(id,0)+1) from tablename

Post Reply

You must Sign In To post reply
Find more Forum Questions on C#, ASP.Net, Vb.Net, SQL Server and more Here
Quick Links For Forum Categories:
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  OOPs  SilverlightIISJQuery

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend