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 autoi开发者_Go百科ncrement 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?

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.

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

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
);