Aspdotnet-Suresh

aspdotnet-suresh offers C#.net articles and tutorials,csharp dot net,asp.net articles and tutorials,VB.NET Articles,Gridview articles,code examples of asp.net 2.0 /3.5,AJAX,SQL Server Articles,examples of .net technologies

How to reset identity column value in sql server

Mar 2, 2012
Introduction:

In this article I will explain how to reset identity column value of table in in
SQL server.

Description:
In previous post I explained how set identity or auto increment column in SQL server. After set identity property on particular column I inserted few records in table and that value automatically increase whenever I inserted data that would be like this

In one situation I deleted all existing records and tried to insert new records in table during that time identity column value starting from previous increased value Ex: Above table contains 8 records after delete all the records if I insert new record CountryID value will start from 9.

To reset identity column value and start value from “1” during insert new records we need to write query to reset identity column value. Check below Query

DBCC CHECKIDENT (Table_Name, RESEED, New_Reseed_Value)
Table_Name is name of your identity column table

RESEED specifies that the current identity value should be changed.

New_Reseed_Value is the new value to use as the current value of the identity column.   
  
EX: DBCC CHECKIDENT ('UserDetails', RESEED, 0)
Once we run the above query it will reset the identity column in UserDetails table and starts identity column value from “1

If you enjoyed this post, please support the blog below. It's FREE!

Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

subscribe by rss Subscribe by RSS subscribe by email Subscribe by Email

17 comments :

Mehtab Ali said...

Knowledgable Post Suresh

abhinav bajpai said...

It,s nice but if i have written a stored procedure then can i write it with that procedure or anywhere else please Clear it...........Thanks

kiran said...

suresh bhai...
i just do a
truncate table mytable
to reset the identity column.

is that correct

Suresh Dasari said...

@kiran..
truncate means your deleting the table completely from your database there is no need to delete table from database to reset identity column just use above statement (DBCC CHECKIDENT (Table_Name, RESEED, New_Reseed_Value)) to reset your identity column

Anonymous said...

hi i got this msg

Checking identity information: current identity value '119', current column value '0'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Suresh Dasari said...

hi,
if we get that message our identity column reset successfully above message saying that during the time of reset your identity column value 119 after reset identity column that value 0. After get that message try to insert records now your column value will start from 1

sai said...

Gud one suresh thanks for ur post

Anonymous said...

hi suresh..thanks for the best articles..
i want to start writing blog.. can u plz tell me how to add demos like in these articles to my blog..

XXXX said...

Hi,i use gridview to report select the dropdownlist the gridview is bind with different query now i got a problem in paging pls help me.

Anonymous said...

How to use sql server tools to work effectivcely

Anonymous said...

Thanks yar keep going yar

hareesh arava said...

hi suresh im facing same problem thanks good post.


thanks&regards
hareesh.a

Anonymous said...

hi suresh .. nice article i just want to know i have table i am missing some values in identity column i have deleted some record from my table

so can i reset my identity column i want serial no. for record

SaiPrasad said...

suresh bhai small query?which i could not understand?
since views are restricting access to rows and colums of the base table?then why we have an option of updating view so that it updates base table?

Aratrika said...

Awesome post..

Anonymous said...

thnks...really thnks...

Josey said...

use
Truncate Table tablename

Give your Valuable Comments

Other Related Posts

© 2010-2012 Aspdotnet-Suresh.com. All Rights Reserved.
The content is copyrighted to Suresh Dasari and may not be reproduced on other websites without permission from the owner.