Reset Identity Column in SQL

I’ll be honest, I am partially writing this because I always forget how to reset the identity column on a table and I want to be able to just pull up my blog and look it up.  Yet again today I had to do this and I couldn’t remember the exact syntax, so here it is.  The next record is going to be the seed value plus 1, or in the case of this example I will use zero so the first record is 1.  That will make my O.C.D. happy to see my table starting with a key value of 1!
Hopefully this will help some people new to T-SQL and be a simple reminder for the veterans out there.
Syntax:
DBCC CHECKIDENT('TableNameHere', RESEED, StartingValue - 1)
Example:
DBCC CHECKIDENT('DimGeography', RESEED, 0)

About the author

Bradley Schacht

Bradley Schacht is a Senior Program Manager on the Microsoft Azure Synapse Analytics team based in Jacksonville, FL. He has worked with Microsoft SQL Server and Azure data services since 2009 as a consultant, trainer, and architect. He has co-authored 4 SQL Server and Power BI books, most recently the Microsoft Power BI Quick Start Guide. Bradley enjoys solving interesting problems and teaching others to use new technology. He frequently presents at community events around the country, is a contributor to sites such as SQLServerCentral.com, and is a member of the Jacksonville SQL Server User Group (JSSUG).

Add comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Bradley Schacht

Bradley Schacht is a Senior Program Manager on the Microsoft Azure Synapse Analytics team based in Jacksonville, FL. He has worked with Microsoft SQL Server and Azure data services since 2009 as a consultant, trainer, and architect. He has co-authored 4 SQL Server and Power BI books, most recently the Microsoft Power BI Quick Start Guide. Bradley enjoys solving interesting problems and teaching others to use new technology. He frequently presents at community events around the country, is a contributor to sites such as SQLServerCentral.com, and is a member of the Jacksonville SQL Server User Group (JSSUG).

Follow Me