Click here to Skip to main content
15,867,686 members
Articles / Database Development / SQL Server
Tip/Trick

How to RESET identity columns in SQL Server

Rate me:
Please Sign up or sign in to vote.
4.93/5 (30 votes)
6 Oct 2011CPOL 284.8K   15   15
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...

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

 
SQL
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)


Written By
Software Developer (Senior)
India India
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
Questiondbcc command to reseed table Pin
green_smith9-Jul-16 1:58
green_smith9-Jul-16 1:58 
GeneralMy vote of 5 Pin
Bill Do3-Sep-13 20:22
Bill Do3-Sep-13 20:22 
GeneralRe: My vote of 5 Pin
Tejas Vaishnav4-Sep-13 21:01
professionalTejas Vaishnav4-Sep-13 21:01 
GeneralMy vote of 5 Pin
CarolV30-Jul-13 2:36
CarolV30-Jul-13 2:36 
Worked like a charm
GeneralRe: My vote of 5 Pin
Tejas Vaishnav30-Jul-13 22:13
professionalTejas Vaishnav30-Jul-13 22:13 
GeneralMy vote of 1 Pin
Strange_Pirate29-Sep-12 0:13
Strange_Pirate29-Sep-12 0:13 
GeneralMy vote of 1 Pin
Strange_Pirate29-Sep-12 0:05
Strange_Pirate29-Sep-12 0:05 
QuestionNeed Your expert advise Pin
Strange_Pirate28-Sep-12 2:01
Strange_Pirate28-Sep-12 2:01 
GeneralReason for my vote of 5 Never used truncate before this :P n... Pin
WongSSJ19-Jan-12 12:01
WongSSJ19-Jan-12 12:01 
GeneralRe: Reason for my vote of 5Never used truncate before this :P n... Pin
Tejas Vaishnav21-Sep-12 2:44
professionalTejas Vaishnav21-Sep-12 2:44 
GeneralReason for my vote of 5 useful information Pin
Thi Dang T11-Oct-11 11:24
Thi Dang T11-Oct-11 11:24 
GeneralRe: Reason for my vote of 5useful information Pin
Tejas Vaishnav21-Sep-12 2:45
professionalTejas Vaishnav21-Sep-12 2:45 
BugRe: Reason for my vote of 5useful information Pin
Strange_Pirate29-Sep-12 0:06
Strange_Pirate29-Sep-12 0:06 
GeneralReason for my vote of 4 Nice Pin
kiran dangar5-Oct-11 1:15
kiran dangar5-Oct-11 1:15 
GeneralRe: Reason for my vote of 4Nice Pin
Tejas Vaishnav21-Sep-12 2:45
professionalTejas Vaishnav21-Sep-12 2:45 

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

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