Click here to Skip to main content
Click here to Skip to main content

Tagged as

How to RESET identity columns in SQL Server

, 6 Oct 2011 CPOL
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

Comments and Discussions

 
GeneralMy vote of 5 PinmemberBill Do3-Sep-13 21:22 
GeneralRe: My vote of 5 PinmemberTejas Vaishnav4-Sep-13 22:01 
GeneralMy vote of 5 PinmemberCarolV30-Jul-13 3:36 
GeneralRe: My vote of 5 PinmemberTejas Vaishnav30-Jul-13 23:13 
GeneralMy vote of 1 PinmemberStrange_Pirate29-Sep-12 1:13 
GeneralMy vote of 1 PinmemberStrange_Pirate29-Sep-12 1:05 
QuestionNeed Your expert advise PinmemberStrange_Pirate28-Sep-12 3:01 
GeneralReason for my vote of 5 Never used truncate before this :P n... PinmemberWongSSJ19-Jan-12 13:01 
GeneralRe: Reason for my vote of 5Never used truncate before this :P n... PinmemberTejas_Vaishnav21-Sep-12 3:44 
GeneralReason for my vote of 5 useful information PinmemberThi Dang T11-Oct-11 12:24 
GeneralRe: Reason for my vote of 5useful information PinmemberTejas_Vaishnav21-Sep-12 3:45 
BugRe: Reason for my vote of 5useful information PinmemberStrange_Pirate29-Sep-12 1:06 
GeneralReason for my vote of 4 Nice Pinmemberkiran dangar5-Oct-11 2:15 
GeneralRe: Reason for my vote of 4Nice PinmemberTejas_Vaishnav21-Sep-12 3: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.141223.1 | Last Updated 7 Oct 2011
Article Copyright 2011 by Tejas Vaishnav
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid