Click here to Skip to main content
11,578,241 members (65,053 online)
Click here to Skip to main content

Tagged as

How to RESET identity columns in SQL Server

, 6 Oct 2011 CPOL 105.9K 10
Rate this:
Please Sign up or sign in to vote.
Resetting an identity column in SQL Server

Introduction

During application development, we often input dummy data into our database for testing purposes. But then we come to the point where we want all records of the table to be deleted and also want to start the identity column values from 0. For this, we delete existing data using the truncate command. This will delete data from table and also reset the identity column value to 0.

Solutions

One way is...

truncate table [table_name]
-- for example
truncate table product

But the truncate command fails to delete the data if there is a relationship given to the table and the identity column is not reset.

The other way is...

In this case, first you need to delete data from the child and the master table.

After deleting data, fire this command and it will reset your identity column to 0.

 
DBCC CHECKIDENT('[table_name]', RESEED, [new_reseed_value])
-- for example
DBCC CHECKIDENT('product', RESEED, 0)

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Tejas Vaishnav
Software Developer (Senior)
India India
No Biography provided

You may also be interested in...

Comments and Discussions

 
GeneralMy vote of 5 Pin
Bill Do3-Sep-13 20:22
memberBill Do3-Sep-13 20:22 
GeneralRe: My vote of 5 Pin
Tejas Vaishnav4-Sep-13 21:01
memberTejas Vaishnav4-Sep-13 21:01 
GeneralMy vote of 5 Pin
CarolV30-Jul-13 2:36
memberCarolV30-Jul-13 2:36 
GeneralRe: My vote of 5 Pin
Tejas Vaishnav30-Jul-13 22:13
memberTejas Vaishnav30-Jul-13 22:13 
GeneralMy vote of 1 Pin
Strange_Pirate29-Sep-12 0:13
memberStrange_Pirate29-Sep-12 0:13 
GeneralMy vote of 1 Pin
Strange_Pirate29-Sep-12 0:05
memberStrange_Pirate29-Sep-12 0:05 
QuestionNeed Your expert advise Pin
Strange_Pirate28-Sep-12 2:01
memberStrange_Pirate28-Sep-12 2:01 
GeneralReason for my vote of 5 Never used truncate before this :P n... Pin
WongSSJ19-Jan-12 12:01
memberWongSSJ19-Jan-12 12:01 
GeneralRe: Reason for my vote of 5Never used truncate before this :P n... Pin
Tejas_Vaishnav21-Sep-12 2:44
memberTejas_Vaishnav21-Sep-12 2:44 
GeneralReason for my vote of 5 useful information Pin
Thi Dang T11-Oct-11 11:24
memberThi Dang T11-Oct-11 11:24 
GeneralRe: Reason for my vote of 5useful information Pin
Tejas_Vaishnav21-Sep-12 2:45
memberTejas_Vaishnav21-Sep-12 2:45 
BugRe: Reason for my vote of 5useful information Pin
Strange_Pirate29-Sep-12 0:06
memberStrange_Pirate29-Sep-12 0:06 
GeneralReason for my vote of 4 Nice Pin
kiran dangar5-Oct-11 1:15
memberkiran dangar5-Oct-11 1:15 
GeneralRe: Reason for my vote of 4Nice Pin
Tejas_Vaishnav21-Sep-12 2:45
memberTejas_Vaishnav21-Sep-12 2:45 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.150603.1 | Last Updated 7 Oct 2011
Article Copyright 2011 by Tejas Vaishnav
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid