SQL Server 2008: ALTER TABLE query

Question

I am getting error for the below code

ALTER TABLE ADM_Roles ALTER COLUMN RoleID int IDENTITY (1, 1)

Incorrect syntax near the keyword IDENTITY.

Answer 1

You cannot change an existing column into an IDENTITY column - you will need to add a new column that has the identity flag:

ALTER TABLE dbo.ADM_Roles 
 ADD NewRoleID INT IDENTITY (1, 1)

If you need to, you can later on drop the old column and rename the new column to the old name:

ALTER TABLE dbo.ADM_Roles DROP COLUMN RoleID

EXEC sp_rename @objName = 'dbo.ADM_Roles.NewRoleID', 
               @newName = 'RoleID', 
               @objType = 'COLUMN'

Answer 2

From the MSDN

You can't alter the existing columns for identity.

You have 2 options,

1. Create a new table with identity & drop the existing table

2. Create a new column with identity & drop the existing column But take special care when these columns have any constraints / relations.

Example approach:

• In this approach you can’t retain the existing data values on the newly created identity column;

• The identity column will hold the sequence of number

  Alter Table Names Add Id_new Int Identity(1,1)
  Go
  
  Alter Table Names Drop Column ID
  Go
  
  Exec sp_rename 'Names.Id_new', 'ID','Column'
  

Answer 3

you have to remove the word "int".

ALTER TABLE ADM_Roles ALTER COLUMN RoleId IDENTITY (1, 1);