CODEWITHSUNDEEP
sqlsql-serverdatabase
dot404
dot404

Reputation: 208

Set autoincrement on a primary key without recreating the tables

I have a table with a primary key that is not set as autoincrement. If I try to set is as autoincrement Management Studio will complain that it cannot do that since it needs to recreate the tables but I cannot allow that since the data is huge. Is possible to set the autoincrement without recreating the tables?

Upvotes: 0

Views: 97

Answers (2)

Rahul Tripathi
Rahul Tripathi

Reputation: 172438

You can't set the autoincrement without recreating the table. The only option is either you can delete the primary key and then create it again. And then set the autoincrement.

You may try like this by creating a temporary table table1:

CREATE TABLE dbo.table1
(
mycolumn INT PRIMARY KEY,
column1 CHAR(10),
column2 CHAR(10)
)

INSERT INTO dbo.table1
SELECT TOP (100) ROW_NUMBER() OVER (ORDER BY (SELECT 0))
FROM master..spt_values v1, master..spt_values v2

Alter it to have an identity column (more or less instant).

BEGIN TRY;
    BEGIN TRANSACTION;

    DECLARE @x nvarchar(max)
    SELECT @x= 
    '
    CREATE TABLE dbo.table2(
        mycolumn INT IDENTITY(' + 
                     CAST(ISNULL(MAX(mycolumn),0)+1 AS VARCHAR) + ',1)  PRIMARY KEY,
        column1 CHAR(10),
        column2 CHAR(10)
        )

        ALTER TABLE dbo.table1 SWITCH TO dbo.table2;
    '       
    FROM dbo.table1
    WITH (TABLOCKX,HOLDLOCK)

    EXEC(@x)

    DROP TABLE dbo.table1;

    EXECUTE sp_rename N'dbo.table2', table1', 'OBJECT';
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
    PRINT ERROR_MESSAGE();
END CATCH;

You may check Allow enabling and disabling of a column's IDENTITY property

Do remember that "turn on" the IDENTITY: is a table rebuild.

Upvotes: 1

NomNomNom
NomNomNom

Reputation: 871

ALTER TABLE [yourTable] DROP COLUMN ID 
ALTER TABLE [yourTable] ADD ID INT IDENTITY(1,1)

this should work, u just need to make sure the column is int.

Upvotes: 0

Related Questions