How to set two columns in SQL Server database as auto increment int with increment seed 100?

Question

How to set two column in SQL Server database as auto increment int with increment seed 100 ?

Answer 1

You can only have one identity column per table, however, there are some ideas and workarounds here

Simulation using a Derived Computed Column

If both "identity" columns are synchronized with each other, or the second identity can be derived from the first using a formula, then a Computed Column might be applicable, e.g. if the second identity is offset by a constant from the actual Identity column:

ALTER TABLE MyTable ADD OtherIdentity AS RealIdentity + 100;

Where RealIdentity is the actual / original IDENTITY column.

Computed Column derived off Identity SqlFiddle example here

Using an Independent Sequence

Another alternative would be to use an independent Sequence (Sql2012 and Later)

CREATE SEQUENCE MySequence START WITH 100;

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

Sequence SqlFiddle example here

Answer 2

Like nonnb says, you can only have one column marked as identity. However, if there is a mathematical relation between the two identity columns, you can use a calculated column. For example, if the second id is equal to the first id plus 500, you could use:

create table t1 (id1 int identity(1,100), id2 as id1 + 500)