CODEWITHSUNDEEP
sqlsql-serverdatabase-design
Ryan Kirkman
Ryan Kirkman

Reputation: 4141

SQL Server: Regenerate value for identity column on row update

SequenceId is an auto-incrementing identity column, but isn't the primary key.

What I want to happen is that when a row is updated, SequenceId is set to the next available value, e.g.:

_____________________
| SequenceId | Name |
| 1          | John |
| 2          | Jane |
¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯

Update John to Jack. SequenceId is set to the next available identity value.

_____________________
| SequenceId | Name |
| 3          | Jack |
| 2          | Jane |
¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯

Is this possible with SQL Server? If not, what would be an alternative?

Upvotes: 1

Views: 1831

Answers (3)

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52107

What I want to happen is that when a row is updated, SequenceId is set to the next available value...

That's not enough, you also want to update the sequence generator, so the next inserted value will be correct.

If you are using MS SQL Server 2012, you can explicitly use the sequence object, instead of the implicit IDENTITY. This allows you to generate the next sequence number without INSERT, like this:

CREATE SEQUENCE MySquence AS INT START WITH 0;

CREATE TABLE MyTable (
    SequenceId INT DEFAULT (NEXT VALUE FOR MySquence),
    Name VARCHAR(50) PRIMARY KEY
);

INSERT INTO MyTable (Name) VALUES ('John');
INSERT INTO MyTable (Name) VALUES ('Jane');

SELECT * FROM MyTable;

    SEQUENCEID  NAME
    1           Jane
    0           John

UPDATE
    MyTable SET SequenceId = NEXT VALUE FOR MySquence,
    Name = 'Jack'
WHERE Name = 'John';

SELECT * FROM MyTable;

    SEQUENCEID  NAME
    2           Jack
    1           Jane

[SQL Fiddle]

On older versions, you can emulate the sequence by having a separate table, containing a single IDENTITY column and then:

  • insert a new row into that table and get the resulting auto-incremented number using INSERT..OUTPUT (or @@IDENTITY),
  • then you can immediately delete the row to save storage.

Or, if you don't have any referential integrity to care about, you can "update" the row by deleting and inserting it.

Upvotes: 1

Jason Goemaat
Jason Goemaat

Reputation: 29214

I don't think you can, at least not easily or cleanly. Why not use a ROWVERSION column? ROWVERSION columns are updated every time the row is updated to a new unique incrementing value in the database. You can create a computed column that converts it to a bigint if you want to index it. The downside is that timestamps are database-wide so you could get skipped numbers if you have multiple tables that use them.

FIDDLE

create table TestRowVersion (
    Id int primary key,
    Name varchar(20),
    BinaryRowVersion ROWVERSION,
    IntegerRowVersion as convert(BIGINT, BinaryRowVersion)
)   

create index IDX_TestRowVersion on TestRowVersion (IntegerRowVersion)

insert into TestRowVersion (Id, Name) values (1, 'Alvin')
insert into TestRowVersion (Id, Name) values (2, 'Betsy')
insert into TestRowVersion (Id, Name) values (3, 'Charles')
select * From TestRowVersion
update TestRowVersion set Name = 'Frank' where Id = 2
select * From TestRowVersion

[EDIT] - updated to use RowVersion since Timestamp is deprecated

Upvotes: 6

CoderMarkus
CoderMarkus

Reputation: 1118

This should work...

SET IDENTITY_INSERT [table] ON

UPDATE [table]
SET Name = 'some name',
    SequenceId = MAX(SequenceId) + 1
WHERE ID = 123

SET IDENTITY_INSERT [table] OFF

Upvotes: 0

Related Questions