CODEWITHSUNDEEP
sql-serverauto-increment
Eric Anastas
Eric Anastas

Reputation: 22213

How can I have two columns in SQL Server auto increment?

I have two columns in a table of a SQL server DB that I would like to autoincrement when new fields are added. However, Managment Studio wont allow me to set two columns to IDENTITY. Is there some other way to do this?

Upvotes: 1

Views: 10003

Answers (3)

Sam
Sam

Reputation: 2917

There can only be one auto increment field per table. But, you could have a calculated field based on the auto increment field. Or, you could have an int field where you manage the sequence by front end code or by a trigger. And also you could use a sequence in SQL Server. 

CREATE SEQUENCE MySequence START WITH 100;

CREATE TABLE MyTable
(
    RealIdentity INT IDENTITY(1,1),
    RandomCol NVARCHAR(100),
    FakeIdentity INT DEFAULT NEXT VALUE FOR MySequence
);

Upvotes: 2

p.campbell
p.campbell

Reputation: 100557

If you wanted the 2nd column to basically be a mirror of the first:

ALTER TABLE dbo.myTable ADD
   foo  AS [rowid]
GO

If you wanted it to apply some math formula to it to achieve some kind of offset:

ALTER TABLE dbo.myTable ADD
    foo  AS ([rowid]+1) * 7 --or whatever you like.
GO

Upvotes: 4

BradC
BradC

Reputation: 39916

You could make the second field a calculated field based on the first.

Or make an INSERT trigger than programatically generates the value for the second.

Upvotes: 4

Related Questions