1

enter image description here

Here is my table and Id is unique constraint...Now I want to add Identity Column in ID .Can anyone help me how to do it with TSQL. This is existing table.

Awesome
  • 560
  • 2
  • 7
  • 18
  • 1
    Duplicate of [How To Create Table with Identity Column](https://stackoverflow.com/questions/10725705/how-to-create-table-with-identity-column) – Brien Foss Jan 08 '18 at 06:02
  • Alter table TBL_NAME alter column ID int NOT NULL IDENTITY(1,1) – Sunil Jan 08 '18 at 06:04
  • 1
    If u want to add an identity to existing column, I think it is not possible. Either u create a fresh table with identity or add a new column with identity to the existing table – Ajay Jan 08 '18 at 06:06
  • 2
    @BrienFoss it's not duplicate of that you have mentioned. I think He wants to alter the existing column as an IDENTITY column. – Kavin Jan 08 '18 at 06:10
  • 1
    Drop `ID` Column and recreate with same name `ID`.. with `not null` constraint with `identity(1,1)` property – Yogesh Sharma Jan 08 '18 at 06:13
  • 1
    @ Yogesh Sharma If i had million columns..do i have to delete it and insert again? ISn't there any other way – Awesome Jan 08 '18 at 06:16
  • @BrienFoss How is this duplicate question...see both question carefully they are different – Awesome Jan 08 '18 at 06:19
  • 2
    @Awesome i am not advocating to delete all columns. Just drop the ID the columns and add same column like `alter table add id int not null identity(1,1)` simple no logic. – Yogesh Sharma Jan 08 '18 at 06:19
  • @YogeshSharma got it.. – Awesome Jan 08 '18 at 06:29
  • @Kavin ok, you got me. Here is your duplicate: [Add an identity to an existing column](https://stackoverflow.com/questions/1049210/adding-an-identity-to-an-existing-column) – Brien Foss Jan 08 '18 at 06:53
  • @BrienFoss haha got it. But you should have mentioned it first itself :) – Kavin Jan 08 '18 at 07:02
  • @BrienFoss haha...question may be but answer aren't – Awesome Jan 08 '18 at 07:44

2 Answers2

7

You can only have 1 Identity Column per Table. So if you don't have one already, Just alter the table and add the Column. Like this

ALTER TABLE YourTableName
ADD IdCol INT IDENTITY(1,1)
Jayasurya Satheesh
  • 7,826
  • 3
  • 22
  • 39
3

You can rename your previous ID column to keep those values if you need them in your application

EXEC sp_rename 'TableName.id', 'oldid', 'COLUMN';

Then add new ID column with Unique constraint as follows

Alter table TableName Add id int identity(1,1) unique not null
Eralper
  • 6,461
  • 2
  • 21
  • 27