CODEWITHSUNDEEP
sqlsql-servert-sql
user4451265
user4451265

Reputation:

How to force ID column to remain sequential even if a recored has been deleted, in SQL server?

I don't know what is the best wording of the question, but I have a table that has 2 columns: ID and NAME. when I delete a record from the table the related ID field deleted with it and then the sequence spoils. take this example:

enter image description here

if I deleted row number 2, the sequence of ID column will be: 1,3,4

How to make it: 1,2,3

Upvotes: 1

Views: 590

Answers (3)

JohnFx
JohnFx

Reputation: 34909

If you want sequential numbers don't store them in the database. That is just a maintenance nightmare, and I really can't think of a very good reason you'd even want to bother.

Just generate them dynamically using tSQL's RowNumber function when you query the data.

The whole point of an Identity column is creating a reliable identifier that you can count on pointing to that row in the DB. If you shift them around you undermine the main reason you WANT an ID.

In a real world example, how would you feel if the IRS wanted to change your SSN every week so they could keep the Social Security Numbers sequential after people died off?

Upvotes: 2

Alex
Alex

Reputation: 21766

In SQL Server identity columns are not guaranteed to be sequential. You can use the ROW_NUMBER function to generate a sequential list of ids when you query the data from the database:

SELECT 
    ROW_NUMBER() OVER (ORDER BY Id) AS SequentialId, 
    Id As UniqueId, 
    Name 
FROM dbo.Details

Upvotes: 2

NoahNoahNoah
NoahNoahNoah

Reputation: 321

ID's are meant to be unique for a reason. Consider this scenario:

**Customers**
id value
1  John
2  Jackie

**Accounts**
id customer_id balance
1  1           $500
2  2           $1000

In the case of a relational database, say you were to delete "John" from the database. Now Jackie would take on the customer_id of 1. When Jackie goes in to check here balance, she will now show $500 short.

Granted, you could go through and update all of her other records, but A) this would be a massive pain in the ass. B) It would be very easy to make mistakes, especially in a large database.

Ids (primary keys in this case) are meant to be the rock that holds your relational database together, and you should always be able to rely on that value regardless of the table.

As JohnFx pointed out, should you want a value that shows the order of the user, consider using a built in function when querying.

Upvotes: 2

Related Questions