To change the identity count

Question

To change the identity count

Tsql-561486

SSC Eights!

Points: 821
More actions
November 20, 2008 at 7:21 am

#201643

Hi
I have a table with identity column.I want to increment the identity without gaps even if a row in delete.
For example:
Identity Value
1 10
2 50
3 80
if i delete the row with identity 3 and if i insert a value after that, identity for that value becomes 4, but i wanted to to be 3.
can any one advice on this?
Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jerry Hung SSChampion Points: 12968 More actions · Answer 1

I don't know how to do this, nor know why this is necessary

Identity is an artificial surrogate key, like someone said, do not give it a meaning 😛 it is just... a number indicating uniqueness

But you may be able to reseed every time you delete

http://www.howtogeek.com/howto/database/reset-identity-column-value-in-sql-server/

For instance, if I wanted to check the next ID value of my orders table, I could use this command:
DBCC CHECKIDENT (orders, NORESEED)
To set the value of the next ID to be 1000, I can use this command:
DBCC CHECKIDENT (orders, RESEED, 999)

SQLServerNewbieMCITP: Database Administrator SQL Server 2005

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

If this is a requirement then you need to implement your own "identity" function with a table that contains the next value. Otherwise you should live with identity as is.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Identity columns uniquely identify individual rows in a table and exist for the benefit of the database, not the business. It doesn't matter to the database if there are gaps or not.

If you have a business requirement for a sequential number without gaps, then create a new column for it - then you can populate it with whatever you like without worrying about the consequences to your database.

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden