5

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
utaco
  • 1,106
  • 8
  • 15

1 Answers1

4

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,
    ....
)
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • But only one identity column per table is allowed. I have 3 columns needs to be set with the same ID while new object created. In your example, I need 2 more IDENTITY(1,1) NOT NULL column. – utaco Jul 22 '19 at 09:35
  • @umtc: as you say - only **one** column can be decorated with the `IDENTITY` value. As of SQL Server 2012, you could possibly creates `SEQUENCES` for other columns, if you **really** need them (but ***why?!?!?!?!*** would you need **three** identity columns in a single table??) – marc_s Jul 22 '19 at 11:04
  • I guess I couldn't explain well. I have integrated 3 projects and one of the projects has many dependencies to IDs. If I have time I would delete this project and start to code from scratch. So I don't need 3 identity columns I was just asking if there is a way to set those other 2 columns with identity column's value without writing update scripts or create a view or a trigger. – utaco Jul 22 '19 at 14:46
  • @umtc: if you want to update columns to a value - trigger is the most logical way to do it. But ***why*** do you need ***3*** columns with identical values?? Can't those apps just all access the **ONE** identity column??? – marc_s Jul 22 '19 at 14:56
  • That's the thing. I can't control those apps and already told the owners to do it. But for now I need a work around and I have used trigger already. I just thought if there is a more efficient way to do this. – utaco Jul 22 '19 at 15:03
  • 1
    @umtc: just thought of a possible way to handle this - see my updated response - use **computed, persisted** columns to "duplicate" the identity column to whatever columns those other applications require - nicely, declaratively, no view, no trigger, no other code needed – marc_s Jul 22 '19 at 15:22
  • 1
    that's what I am exactly looking for. Thank you sir! – utaco Jul 23 '19 at 10:27