CODEWITHSUNDEEP
sqlsql-serverdatabase
HamSoft
HamSoft

Reputation: 67

SQL Server: update primary key after different insert statement

I want to update the primary key in SQL Server. I executed three insert statement in my table. And the primary key column is like this.

Id  NUM
-------
1   T1
2   T2
3   T3
7   T4
8   T5
9   T6
13  T7
14  T8
15  T9
16  T10

I want to update the column Id to get this:

Id  NUM
-------
1   T1
2   T2
3   T3
4   T4
5   T5
6   T6
7   T7
8   T8
9   T9
10  T10

Can someone please guide me on how to resolve this?

Thanks in advance.

Upvotes: 1

Views: 374

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

Don't do it! Remember the purpose of primary keys. They are non-NULL keys that uniquely identify each row in a table. They serve multiple uses. In particular, they are used for foreign key references. And, in SQL Server, they are (by default) used to sort the original data.

The identity column provides an increasing sequence of numbers, balancing the objective of an increasing number with performance. As a result, gaps appear for various reasons, but particularly due to deletes, failed inserts, and performance optimizations in a parallel environment.

In general, the aesthetics of gapless numbers are less important than the functionality provided by the keys -- and gaps have basically no impact on performance.

And, in particular, changing primary keys can be quite expensive:

  • The data on the pages needs to be re-sorted for the clustered index. This is true even when the ordering does not change.
  • Foreign keys have to be updated, if you have cascading updates set for the indexes.
  • Foreign keys are invalidated -- a really bad thing -- if you happen not to have the proper foreign key definitions.

And, even if you do go through the trouble of doing this, gaps are going to appear in the future, due to deletes, failed inserts, and database optimizations.

Upvotes: 2

Squirrel
Squirrel

Reputation: 24763

use row_number() to generate the new sequence. You need to order by NUM ignoring the first character T

UPDATE  t
SET     Id = rn
FROM
(
    SELECT  Id, NUM, 
            rn = row_number() OVER (ORDER BY convert(int, 
                                             substring(NUM, 2, len(NUM) - 1) ) )
    FROM    yourtable
) t

Upvotes: 0

Related Questions