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

SQL Server - Reset Identity Column Value to 1 in SQL Database

Nov 25, 2013
Introduction:

Here I will explain how to reset identity column value in SQL server or change or rest identity column value to 1 in SQL server. or reseed identity column value in 
SQL server.
Description:

In previous post I explained how set identity or auto incrementcolumn 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

4 comments :

Thiyagarajan said...

Nice..

Sai said...

Can we truncate the table? As far as I know, if we truncate the table the identity coloum is set to 1. All the objects which are depend on the table will also gone.

AllBhktiSongs said...

it's very use full for me

can you provide the code for
count the total money of parent node of tree and number of pair under particular parent according to a particular pattern 1:2or2:1,1:1

Anonymous said...

instead of that we can use Truncate Table [TableName]

Give your Valuable Comments

Note: Only a member of this blog may post a comment.

© 2015 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.