Easy way to change Column ID (primary key) of a table to Auto increment

Question

I started to use SQL Server recently and I still don't know the best way to do some things.

I created all the tables with a column ID as the primary key. Now when I try to insert values I get the following error:

"Cannot insert the value NULL into column 'id', table 'Project.dbo.Table'; column does not allow nulls. INSERT fails. The statement has been terminated."

What is a good, simple solution to this problem?

Answer 1

If your table is empty, you can drop and recreate the table again and add IDENTITY(1,1) to the column definition of id to make your id column in Project.dbo.Table auto increment.

Something like this.

CREATE TABLE Project.dbo.Table
(
id int IDENTITY(1,1) NOT NULL,
col2 VARCHAR(50) NULL,
...
...
);

You can either add PRIMARY KEY with the column name like this id int IDENTITY(1,1) NOT NULL PRIMARY KEY, or at the end after defining all columns like this CONSTRAINT PK_Table PRIMARY KEY(id)

SQL Server does not allow you to add IDENTITY by altering a table. use GO between IF EXISTS ...DROP TABLE and the CREATE TABLE statements to run those statements in separate batches.

Answer 2

My preferred method for a non-empty table is to use the ALTER TABLE ... SWITCH method which is a meta-data-only operation.

This is a simple test-bed setup, consisting of the initial table, named dbo.SwitchTest1, and the target table containing an IDENTITY, named dbo.SwitchTest2:

USE tempdb;
IF OBJECT_ID('dbo.SwitchTest1') IS NOT NULL
DROP TABLE dbo.SwitchTest1;
IF OBJECT_ID('dbo.SwitchTest2') IS NOT NULL
DROP TABLE dbo.SwitchTest2;
GO
CREATE TABLE dbo.SwitchTest1
(
    ID INT NOT NULL 
        CONSTRAINT PK_SwitchTest1
        PRIMARY KEY CLUSTERED
    , SomeVal VARCHAR(50) NOT NULL
);
CREATE TABLE dbo.SwitchTest2
(
    ID INT NOT NULL 
        CONSTRAINT PK_SwitchTest2
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , SomeVal VARCHAR(50) NOT NULL
);

INSERT INTO dbo.SwitchTest1(ID, SomeVal)
VALUES (1, 'This is a test');

Here, we use the ALTER TABLE ... SWITCH method to replace the data in dbo.SwitchTest2 table with data from dbo.SwitchTest1, while maintaining the definition of dbo.SwitchTest2 including the IDENTITY column. Although not strictly necessary, you probably want to rename any constraints included in the definition of dbo.SwitchTest2 after the fact to match what they used to be called in dbo.SwitchTest1. I've done that below for the primary key.

BEGIN TRANSACTION
BEGIN TRY
    ALTER TABLE dbo.SwitchTest1 SWITCH TO dbo.SwitchTest2;
    DROP TABLE dbo.SwitchTest1;
    EXEC sp_rename @objname = 'dbo.SwitchTest2'
        ,@newname = 'SwitchTest1', @objtype = 'object';
    EXEC sp_rename @objname = 'PK_SwitchTest2'
        , @newname = 'PK_SwitchTest1', @objtype = 'object';
    EXEC sp_recompile @objname = 'dbo.SwitchTest1';
    DBCC CHECKIDENT('dbo.SwitchTest1');
    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION
END CATCH

In the code above, I've also added a call to sp_recompile - this causes stored procedures, triggers, and user-defined functions to be recompiled the next time that they are run. It does this by dropping the existing plan from the procedure cache forcing a new plan to be created the next time that the procedure or trigger is run. This is A Good Thing™ since the object_id for the table dbo.SwitchTest1 table has changed. Also, I'm running DBCC CHECKIDENT('<tablename>'); to cause the IDENTITY() column to be updated with the next valid value to be used by an INSERT operation.

Here, we're inserting a value into the SomeVal column without specifying a value for the ID column, which is now an IDENTITY column:

INSERT INTO dbo.SwitchTest1 (SomeVal)
VALUES ('This is another test');

And the results:

SELECT *
FROM dbo.SwitchTest1;

Answer 3

As you are on SQL Server 2012 you could also create a sequence object per table and apply it to the desired auto incrementing column with a default constraint that calls next value for.

The two methods have some differences in behaviour which you may or may not find desirable.

1. identity columns are not updatable.
2. Greater control over cache size with sequence
3. The sequence value can be acquired before the insert if needed but if using sequences the various identity related functions obviously won't work.
4. Inserts of large amounts of rows can be faster with identity due to fewer log buffer flushes.

Answer 4

Resolved!

Just need to click on Properties inside the table, and in "Identity column: " insert the name of a column