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


Top 5 Contributors of the Month
david stephan

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


Hello:
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
table.

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
table.

The DB is MS SQL Server 2000.


Thanks and appreciate any help

Responses
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   

hi

thanx,

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

id
---
1



when you run the query again the output will be

id
--
1
2

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
JavaScript/VBScriptBiztalkWPFPatten/PracticesWCFOthers
www.DotNetSpark.comUnAnsweredAll

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