Click here to Skip to main content
Click here to Skip to main content

Reseed Identity of Table - Table Missing Identity Values - Gap in Identity Column

, 8 Apr 2009 CPOL
Rate this:
Please Sign up or sign in to vote.
Reseed Identity of Table - Table Missing Identity Values - Gap in Identity Column

Introduction

Some time ago, I was helping one of my junior developers who presented me with an interesting situation. He had a table with Identity Column. Because of some reasons, he was compelled to delete few rows from the table. On inserting new rows in the table, he noticed that the rows started from the next identity value which created a gap in the identity value. His application required all the identities to be in sequence, so this was certainly not a small issue for him.

Solution

The solution to this issue regarding gap in identity column is very simple. Let us first take a look at his application’s situation wherein there is a missing identity and then we will move on to the solution.

Developers can easily deter the above issue by avoiding a gap in sequence of identity column through two additional SQL tricks of reseeding identity. We will now see the same example with the solution to the above gap issue. On deleting records, the table was reseeded with identity, which was deleted.

You can download the complete SQL script here.

I hope this solution is clear to all my readers and they will use it to avoid problems related to gap in identity column. Do send me your feedback on this article and let me know if you all need further explanation.

Reference

kick it on DotNetKicks.com

History

  • 8th April, 2009: Initial post

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

pinaldave
Founder http://blog.SQLAuthority.com
India India
Pinal Dave is a Microsoft Technology Evangelist (Database and BI). He has written over 2200 articles on the subject on his blog at http://blog.sqlauthority.com. Along with 8+ years of hands on experience he holds a Masters of Science degree and a number of certifications, including MCTS, MCDBA and MCAD (.NET). He is co-author of two SQL Server books - SQL Server Programming, SQL Wait Stats and SQL Server Interview Questions and Answers. Prior to joining Microsoft he was awarded Microsoft MVP award for three continuous years for his contribution in community.
Follow on   Twitter

Comments and Discussions

 
RantOn another note PinmemberChrisC(@ncmail)13-Apr-09 11:34 
GeneralLearning opportunities are everywhere PinmemberChrisC(@ncmail)13-Apr-09 11:13 
GeneralSET IDENTITY_INSERT tb_User ON Pinmemberspoodygoon8-Apr-09 11:57 
GeneralForeign Keys and Referential Integrity Pinmemberdelyk8-Apr-09 5:46 
GeneralRe: Foreign Keys and Referential Integrity PinmemberKev@Coastal14-Apr-09 0:55 
GeneralGood one... PinmemberVirendra Dugar8-Apr-09 0:38 

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

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

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.141223.1 | Last Updated 8 Apr 2009
Article Copyright 2009 by pinaldave
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid