How to alter column to identity(1,1)

Question

How to alter column to identity in a table

Answer 1

Hi @César Morgado

There is no straightforward T-SQL like ALTER TABLE… to add an Identity Property to an existing column in a table.

If the column is empty, then create a new column with identity(1,1) and then drop the existing column.

If already exist value in that column, then you might consider using SEQUENCE, like this:

CREATE SEQUENCE test_sequence_name START WITH  max_existed_value_in_column_plus_one INCREMENT BY 1;

ALTER TABLE your_table_name ADD CONSTRAINT constraint_name DEFAULT NEXT VALUE FOR test_sequence_name FOR column_name

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 2

As Cosmog says, there is no straight-forward way to do this, but there is a trick with ALTER TABLE SWITCH which makes it possible to do without having to reload the table:

CREATE TABLE alfa (a int NOT NULL PRIMARY KEY,
                   b varchar(23) NOT NULL)
INSERT alfa(a, b) VALUES(1, 'one'), (2, 'two'), (3, 'three')
go
CREATE TABLE beta (a int NOT NULL IDENTITY PRIMARY KEY,
                   b varchar(23) NOT NULL)
go
ALTER TABLE alfa SWITCH TO beta
go
SELECT * FROM beta
DBCC CHECKIDENT (beta)
DROP TABLE alfa
EXEC sp_rename 'beta', 'alfa'
go
INSERT alfa(b) VALUES( 'four')
go
SELECT a, b FROM alfa
go
DROP TABLE alfa