I have a table table1 in SQL server 2008 and it has records in it.

I want the primary key table1_Sno column to be an auto-incrementing column. Can this be done without any data transfer or cloning of table?

I know that I can use ALTER TABLE to add an auto-increment column, but can I simply add the AUTO_INCREMENT option to an existing column that is the primary key?

upvote
  flag
Not sure you can do this: see //allinonescript.com/questions/4862385/… – Femi

6 Answers 11

No, you can not add an auto increment option to an existing column with data, I think the option which you mentioned is the best.

Have a look here.

If you don't want to add a new column, and you can guarantee that your current int column is unique, you could select all of the data out into a temporary table, drop the table and recreate with the IDENTITY column specified. Then using SET IDENTITY INSERT ON you can insert all of your data in the temporary table into the new table.

up vote 60 down vote accepted

Changing the IDENTITY property is really a metadata only change. But to update the metadata directly requires starting the instance in single user mode and messing around with some columns in sys.syscolpars and is undocumented/unsupported and not something I would recommend or will give any additional details about.

For people coming across this answer on SQL Server 2012+ by far the easiest way of achieving this result of an auto incrementing column would be to create a SEQUENCE object and set the next value for seq as the column default.

Alternatively, or for previous versions (from 2005 onwards), the workaround posted on this connect item shows a completely supported way of doing this without any need for size of data operations using ALTER TABLE...SWITCH. Also blogged about on MSDN here. Though the code to achieve this is not very simple and there are restrictions - such as the table being changed can't be the target of a foreign key constraint.

Example code.

Set up test table with no identity column.

CREATE TABLE dbo.tblFoo 
(
bar INT PRIMARY KEY,
filler CHAR(8000),
filler2 CHAR(49)
)


INSERT INTO dbo.tblFoo (bar)
SELECT TOP (10000) 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;

    /*Using DBCC CHECKIDENT('dbo.tblFoo') is slow so use dynamic SQL to
      set the correct seed in the table definition instead*/
    DECLARE @TableScript nvarchar(max)
    SELECT @TableScript = 
    '
    CREATE TABLE dbo.Destination(
        bar INT IDENTITY(' + 
                     CAST(ISNULL(MAX(bar),0)+1 AS VARCHAR) + ',1)  PRIMARY KEY,
        filler CHAR(8000),
        filler2 CHAR(49)
        )

        ALTER TABLE dbo.tblFoo SWITCH TO dbo.Destination;
    '       
    FROM dbo.tblFoo
    WITH (TABLOCKX,HOLDLOCK)

    EXEC(@TableScript)


    DROP TABLE dbo.tblFoo;

    EXECUTE sp_rename N'dbo.Destination', N'tblFoo', 'OBJECT';


    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
    PRINT ERROR_MESSAGE();
END CATCH;

Test the result.

INSERT INTO dbo.tblFoo (filler,filler2) 
OUTPUT inserted.*
VALUES ('foo','bar')

Gives

bar         filler    filler2
----------- --------- ---------
10001       foo       bar      

Clean up

DROP TABLE dbo.tblFoo
1 upvote
  flag
+1 Neat trick, I wasn't aware of this workaround. Seems pretty obscure and undocumented, I don't think folks should get down-voted if they didn't know about it. – Aaron Bertrand
upvote
  flag
@AaronBertrand - Agree. Wasn't my DV on the other question. – Martin Smith
upvote
  flag
Should be noted also that version is important. SWITCH won't work pre-2005. I wish tagging sql-server also required specifying a minimum version. Not saying that for the benefit of this question, but the one that might get closed as a duplicate of this one. – Aaron Bertrand
upvote
  flag
@AaronBertrand - BTW also useful technique for other cases such as altering the ANSI_NULLS option or working around this bug - drop duplicate unique constraint on ROWGUID column on FILESTREAM table – Martin Smith
upvote
  flag
@AaronBertrand - Reversed now whoever it was. Maybe they read the reference to sys.syscolpars and thought my answer was going to do that. – Martin Smith
upvote
  flag
Doesn't the use of SWITCH require that the table already be partitioned? – RBarryYoung
1 upvote
  flag
@RBarryYoung - No. Every table is regarded as being at least a single partition. This works on all editions of SQL Server. It just changes the object_id in sys.partitions to point to the new object. – Martin Smith
upvote
  flag
This not work if tblFoo has it's primary key as a foreign key in another table. – Bernardo Pacheco
upvote
  flag
@BernardoPacheco - Agreed this method doesn't work then so you'd need to consider whether it was worth dropping and recreating the FK constraint (meaning the FK would then need to be revalidated on all rows). – Martin Smith
upvote
  flag
@MartinSmith you are right, that's what I did. – Bernardo Pacheco

SQL Server: How to set auto-increment on a table with rows in it:

This strategy physically copies the rows around twice which can take a much longer time if the table you are copying is very large.

You could save out your data, drop and rebuild the table with the auto-increment and primary key, then load the data back in.

I'll walk you through with an example:

Step 1, create table foobar (without primary key or auto-increment):

CREATE TABLE foobar(
    id int NOT NULL,
    name nchar(100) NOT NULL,
)

Step 2, insert some rows

insert into foobar values(1, 'one');
insert into foobar values(2, 'two');
insert into foobar values(3, 'three');

Step 3, copy out foobar data into a temp table:

select * into temp_foobar from foobar

Step 4, drop table foobar:

drop table foobar;

Step 5, recreate your table with the primary key and auto-increment properties:

CREATE TABLE foobar(
    id int primary key IDENTITY(1, 1) NOT NULL,
    name nchar(100) NOT NULL,
)

Step 6, insert your data from temp table back into foobar

SET IDENTITY_INSERT temp_foobar ON
INSERT into foobar (id, name) select id, name from temp_foobar;

Step 7, drop your temp table, and check to see if it worked:

drop table temp_foobar;
select * from foobar;

You should get this, and when you inspect the foobar table, the id column is auto-increment of 1 and id is a primary key:

1    one
2    two
3    three
upvote
  flag
When I try this I get the error "Table 'temp_foobar' does not have the identity property. Cannot perform SET operation." Am I missing a step? – dallin
upvote
  flag
To get this to work, I changed it to "SET IDENTITY_INSERT foobar ON;" as its own statement. In addition, I had to set the NOT FOR REPLICATION option to Yes or I got a "Explicit value must be specified for identity column" error on the INSERT statement. – dallin

Yes, you can. Go to Tools > Designers > Table and Designers and uncheck "Prevent Saving Changes That Prevent Table Recreation".

upvote
  flag
The question specifies "without data transfer". SSMS creates a new table and copies all the data into it. – Martin Smith

If you want to do this via the designer you can do it by following the instructions here "Save changes is not permitted" when changing an existing column to be nullable

Not the answer you're looking for? Browse other questions tagged or ask your own question.