CODEWITHSUNDEEP
sqlsql-server
Mehdi Glida
Mehdi Glida

Reputation: 557

How to make an auto-incrementing id column use missing numbers in SQL Server?

Say I have a table with 100 rows and an auto-incrementing id column, and I delete the row with id = 15. How can I add a new row with id = 15 instead of id = 101?

Upvotes: 0

Views: 368

Answers (2)

The Impaler
The Impaler

Reputation: 48770

No, don't do it.

Generated primary keys are used to produce unique values, that are of internal use. They are not supposed to "sexy" or nice looking. That is NOT their purpose; the PK purpose is uniqueness. If you are concerned about the specific values, then it means you are exposing the PK to the external world... something that raises a lot of red flags.

If you need a value to expose, don't use the PK fot this, but create a secondary column for it. Its solely purpose in the world is to be exposed. This value can have a nice formatting with dashes (like the SSN), prefixes, suffixes, etc.

Upvotes: 1

Rono
Rono

Reputation: 3361

If you have a table with an identity key field you could use this:

SET IDENTITY_INSERT YourTableName ON
INSERT INTO YourTableName (ID, Other) VALUES (15, 'Whatever')
SET IDENTITY_INSERT YourTableName OFF

Upvotes: 0

Related Questions