Reset identity column value in SQL Server

Posted by Akiii under Sql Server category on 7/10/2012 | Points: 40 | Views : 1910

Post Code |

Search Codes |

Code Home

Suppose you have a table with 10 rows in it(below is the table structure).

id  int,
name nvarchar(50)

You have deleted all the rows, now when you insert further data in it. It will generate the id 11. Now if you want to reset the identity column to 0. What will you do ?

Execute the code below against a table and it will reset your identity column to 0 :-

DBCC CHECKIDENT ('table_name', RESEED, 0)

Thanks and Regards
Akiii

Alert Moderator

Bookmark It

Comments or Responses

Posted by: Krishna12345 on: 7/10/2012 Level:Starter | Status: [Member] | Points: 10

Thanks
This was a great help.
Now can i use it in a function which will reset the Identity column based on the maximum value of the Identity column.
So that i don't have to delete all my data.

Posted by: Akiii on: 7/10/2012 Level:Bronze | Status: [Member] | Points: 10

Hi @Krishna12345...

You want to delete all the rows from a table and start from the beginning that is 0 ?

Akiii

Latest Code Snipptes