ALTER COLUMN with INTEGER Datatype, NOT NULL, PRIMARY KEY, auto increment

Question

I have a MySQL QUERY and working fine. QUERY IS:

ALTER TABLE run 
CHANGE RN_RUN_ID RN_RUN_ID INT(11) NOT NULL AUTO_INCREMENT, 
ADD PRIMARY KEY (RN_RUN_ID);

I tried to execute same Query and modified in MSSQL which is throwing error. MSSQL QUERY IS:

ALTER TABLE run ALTER COLUMN RN_RUN_ID 
RN_RUN_ID INT NOT NULL AUTO_INCREMENT,
ADD PRIMARY KEY (RN_RUN_ID);

ERROR IS:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'AUTO_INCREMENT'.

Can you tell me what I did wrong in this query.

Answer 1

You cant do it directly.

See this post: Adding an identity to an existing column

First answer.

Answer 2

In SQL Server there is no such AUTO_INCREMENT option. Please look at the docs. The equivalent is IDENTITY.

You cannot modify a column directly to become IDENTITY. You need to create a new column on the table, or create a new table and rename it. It has some tricky parts (allowing the insert in the identity column, renaming a column, and some other things). Look this SO answer.

There are also differences on the syntax for adding a PK to a table:

ALTER TABLE run
ADD CONSTRAINT PK PRIMARY KEY (rn_run_id)

If you have acces to SQL Server Management Studio you can get the full script easyly:

• add a new diagram to the database
• add the table to the diagram (rigth click on the window, and use the contextual menu)
• save the diagram (you can delete it later)
• right click on the table and choose custom view
• right click again on the table, and choose "modify custom view". Add the identity, identity initial value, identity increment, key, etc. required elements to the custom view
• modify the column properties in the diagram
• go to the "Table designer" menu, and choose the last option "Generate change scripts". You'll get an script which does all the changes for you. For example:

An example of doing this process (by modifying the column id of the table test):

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_test
    (
    id int NOT NULL IDENTITY (10, 2)
    )  ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_test SET (LOCK_ESCALATION = TABLE)
GO
SET IDENTITY_INSERT dbo.Tmp_test ON
GO
IF EXISTS(SELECT * FROM dbo.test)
     EXEC('INSERT INTO dbo.Tmp_test (id)
        SELECT id FROM dbo.test WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_test OFF
GO
DROP TABLE dbo.test
GO
EXECUTE sp_rename N'dbo.Tmp_test', N'test', 'OBJECT' 
GO
COMMIT

NOTE: the name of the menu options can be different, i don't have an English SSMS at hand right now

Relevant docs:

• IDENTITY
• DBCC CHECKIDENT
• SET IDENTITY_INSERT
• ALTER TABLE