How to set auto incremented id to another column with using Peta Poco

Question

I use C#, SQL Server and PetaPoco which auto increments the ID by default. I just want to know that if it is possible to set this autoincremented id to another column.

I have 2 more columns needs to be set with the exact value of ID. Is it possible to do this inside the SQL CREATE TABLE statement or doing some hook before or after ExecuteNonQuery? I don't want to create a view or a trigger if it's possible.

Answer 1

Yes, of course - the CREATE TABLE allows you to define which column will be the auto-increment column, by adding the IDENTITY keyword to the column definition. This works for any integer-based column, or columns of type DECIMAL(p, 0) (with a 0 scale - no after-the-dot digits).

E.g.

CREATE TABLE dbo.YourTable
(
    ID INT NOT NULL,
    SomeOtherColumn BIGINT IDENTITY(1,1) NOT NULL,
    ....
)

See the MS docs on CREATE TABLE for the full and complete details

UPDATE: so you have 3 separate third-party applications, that all access this table, and all expect to find specific columns - one of which you can make the identity column. The two others could be computed, persisted "copies" of that identity column - something like this:

CREATE TABLE dbo.YourTable
(
    ID INT NOT NULL,
    YourIdentityColumn BIGINT IDENTITY(1,1) NOT NULL,
    ....
    SpecialColumnForApp2 AS YourIdentityColumn PERSISTED,
    SpecialColumnForApp3 AS YourIdentityColumn PERSISTED,
    ....
)