By Kirschstein


2009-06-26 13:41:21 8 Comments

I need to change the primary key of a table to an identity column, and there's already a number of rows in table.

I've got a script to clean up the IDs to ensure they're sequential starting at 1, works fine on my test database.

What's the SQL command to alter the column to have an identity property?

19 comments

@jdisla 2019-02-26 22:26:31

If you happen to be using Visual Studio 2017+

  1. In Server Object Explorer right-click on your table and select "view code"
  2. Add the modifier "IDENTITY" to your column
  3. Update

This will do it all for you.

@OTAR 2015-06-23 12:38:02

Consider to use SEQUENCE instead of IDENTITY.

IN sql server 2014 (I don't know about lower versions) you can do this simply, using sequence.

CREATE SEQUENCE  sequence_name START WITH here_higher_number_than_max_existed_value_in_column INCREMENT BY 1;

ALTER TABLE table_name ADD CONSTRAINT constraint_name DEFAULT NEXT VALUE FOR sequence_name FOR column_name

From here: Sequence as default value for a column

@Justin Grant 2009-11-13 17:52:06

In SQL 2005 and above, there's a trick to solve this problem without changing the table's data pages. This is important for large tables where touching every data page can take minutes or hours. The trick also works even if the identity column is a primary key, is part of a clustered or non-clustered index, or other gotchas which can trip up the the simpler "add/remove/rename column" solution.

Here's the trick: you can use SQL Server's ALTER TABLE...SWITCH statement to change the schema of a table without changing the data, meaning you can replace a table with an IDENTITY with an identical table schema, but without an IDENTITY column. The same trick works to add IDENTITY to an existing column.

Normally, ALTER TABLE...SWITCH is used to efficiently replace a full partition in a partitioned table with a new, empty partition. But it can also be used in non-partitioned tables too.

I've used this trick to convert, in under 5 seconds, a column of a of a 2.5 billion row table from IDENTITY to a non-IDENTITY (in order to run a multi-hour query whose query plan worked better for non-IDENTITY columns), and then restored the IDENTITY setting, again in less than 5 seconds.

Here's a code sample of how it works.

 CREATE TABLE Test
 (
   id int identity(1,1),
   somecolumn varchar(10)
 );

 INSERT INTO Test VALUES ('Hello');
 INSERT INTO Test VALUES ('World');

 -- copy the table. use same schema, but no identity
 CREATE TABLE Test2
 (
   id int NOT NULL,
   somecolumn varchar(10)
 );

 ALTER TABLE Test SWITCH TO Test2;

 -- drop the original (now empty) table
 DROP TABLE Test;

 -- rename new table to old table's name
 EXEC sp_rename 'Test2','Test';

 -- update the identity seed
 DBCC CHECKIDENT('Test');

 -- see same records
 SELECT * FROM Test; 

This is obviously more involved than the solutions in other answers, but if your table is large this can be a real life-saver. There are some caveats:

  • As far as I know, identity is the only thing you can change about your table's columns with this method. Adding/removing columns, changing nullability, etc. isn't allowed.
  • You'll need to drop foriegn keys before you do the switch and restore them after.
  • Same for WITH SCHEMABINDING functions, views, etc.
  • new table's indexes need to match exactly (same columns, same order, etc.)
  • Old and new tables need to be on the same filegroup.
  • Only works on SQL Server 2005 or later
  • I previously believed that this trick only works on the Enterprise or Developer editions of SQL Server (because partitions are only supported in Enterprise and Developer versions), but Mason G. Zhwiti in his comment below says that it also works in SQL Standard Edition too. I assume this means that the restriction to Enterprise or Developer doesn't apply to ALTER TABLE...SWITCH.

There's a good article on TechNet detailing the requirements above.

UPDATE - Eric Wu had a comment below that adds important info about this solution. Copying it here to make sure it gets more attention:

There's another caveat here that is worth mentioning. Althought the new table will happily receive data from the old table, and all the new rows will be inserted following a identity pattern, they will start at 1 and potentially break if the said column is a primary key. Consider running DBCC CHECKIDENT('<newTableName>') immediately after switching. See msdn.microsoft.com/en-us/library/ms176057.aspx for more info.

If the table is actively being extended with new rows (meaning you don't have much if any downtime between adding IDENTITY and adding new rows, then instead of DBCC CHECKIDENT you'll want to manually set the identity seed value in the new table schema to be larger than the largest existing ID in the table, e.g. IDENTITY (2435457, 1). You might be able to include both the ALTER TABLE...SWITCH and the DBCC CHECKIDENT in a transaction (or not-- haven't tested this) but seems like setting the seed value manually will be easier and safer.

Obviously, if no new rows are being added to the table (or they're only added occasionally, like a daily ETL process) then this race condition won't happen so DBCC CHECKIDENT is fine.

@Martin Smith 2011-08-24 11:12:26

+1 Do you know the origin of this "trick"? I first came across it in the workarounds to a connect item but I see your answer predates this.

@Justin Grant 2011-08-29 06:05:44

If my memory is correct, I got the idea from this article: sqlservercentral.com/articles/T-SQL/61979

@Mason G. Zhwiti 2011-10-13 16:04:53

FYI, this appears to also work on the Standard version of SQL 2008 R2. Perhaps they enabled this feature just like they've now enabled the ability to turn on backup compression.

@Justin Grant 2011-10-20 01:08:53

Mason - great observation! I updated my answer accordingly. I'd assumed that, because partitions aren't supported in SQL Standard that ALTER TABLE...SWITCH wouldn't be supported either. But given that you can use this command on non-partitioned tables, it makes sense that you can also run it on Standard Edition. Thanks for the correction.

@jbatista 2014-10-24 11:19:39

This is very useful but it doesn't recreate the IDs. It only takes the old ones into the new table. In my case I want to add a ID Identity(1,1) column to an existent table, I don't have the ID values in the old table to fill it...

@Justin Grant 2014-10-24 22:00:03

@jbatista - the OP's question stated that he already had a primary key on the table and already could ensure the right values, but he simply wanted to change it to be an IDENTITY column. My answer above is focused that narrow use-case: how to add the IDENTITY to a column without actually changing any data. The approach I document above is a huge time-saver for large tables. If you need to change data, then you'll need to use other solutions.

@Fernando Fabreti 2016-07-22 18:03:17

this is good advise! Works fast!

@Eric Wu 2016-08-08 15:13:51

There's another caveat here that is worth mentioning. Althought the new table will happily receive data from the old table, and all the new rows will be inserted following a identity pattern, they will start at 1 and potentially break if the said column is a primary key. Consider running DBCC CHECKIDENT('<newTableName>') immediately after switching. See msdn.microsoft.com/en-us/library/ms176057.aspx for more info.

@Philippe 2016-08-16 20:18:58

This is a great answer! Also note that nullability of columns must be the same. So if you need to change a column nullability, you will have to do it at a later step. Same goes for PK constraints. I also change the identity value in the table creation to match the current maximum : IDENTITY (maxID+1, 1)

@Alex Peshik 2018-06-25 10:44:54

If the table is huge enough column id int NOT NULL have to be updated with not null incremental values before SWITCH. Which is not 5 seconds, unfortunately. Otherwise this will be error message: ALTER TABLE SWITCH statement failed because column 'id' does not have the same nullability attribute in tables 'Test' and 'Test2'.

@Justin Grant 2018-06-25 15:19:06

@Alex Peshik - you're correct, this solution (and the OP's question) is about making an existing primary key club into an IDENTITY column. If the column is already a primary key then it's guaranteed to already be NOT NULL.

@SSS 2018-06-29 04:07:53

I wish I could give more than one upvote. This answer has saved my bacon several times now!

@Rinku 2016-02-02 11:53:14

As I understood in normal cases we are creating a table with Primary key which is having Identity property
So Rename or Delete a column which is associated with Primary Key constraint will not be possible because constraint Rules are validating column structure.
Tto achieve this we have to process some steps in the following way:
Let us assume TableName = 'Employee' and ColumnName = 'EmployeeId'

1. Add new column 'EmployeeId_new' in the 'Employee' table
ALTER TABLE Employee ADD EmployeeId_new INT IDENTITY(1,1)

  1. Now remove column 'EmployeeId' from 'Employee' table
    ALTER TABLE Employee DROP COLUMN EmployeeId

  2. This will throw error because of Primary Key Constraint rules are applicable and validating column structure.
    *### 'Msg 5074, Level 16, State 1, Line 1 The object [PK_dbo.Employee] is dependent on colmn [EmployeeId].'###

  3. So we have to remove the Primary Key constraint first from the table 'Employee' then we can remove the column
    ALTER TABLE Employee DROP constraint [PK_dbo.Employee]

  4. Now we can remove the column 'EmployeeId' from 'Employee' table as did in the previous step where we got error
    ALTER TABLE Employee DROP COLUMN EmployeeId

  5. Now Column 'EmployeeId' removed from table So we will Rename the newly added new column 'EmployeeId_new' with 'EmployeeId'
    sp_rename 'Employee.EmployeeId', 'EmployeeId_new', 'COLUMN'

  6. To rearrange the table in the same form as It was, we have to add Primary Key Constraint for the column 'EmployeeId'
    ALTER TABLE Employee add constraint [PK_dbo.Employee] primary key (EmployeeId)

8. Now the table 'Employee' with 'EmployeeId' is modified for Identity rules along with existing primary key constraint

@James Drinkard 2013-08-29 22:05:08

I'm a java developer that happened to get on a team without a DBA and one where as a developer, I can't get DBA rights. I was tasked with moving an entire schema between two databases, so without having a DBA, I had to do it and do it by running scripts, not being able to use the GUI in SQL Server 2008 because I didn't have admin privileges.

Everything was moved without issue, however, when running a stored procedure on the new schema.table, I found I lost the identity field in a table. I double checked the script that created the table and it was there, however, SQL Server didn't get it when I ran the script. I was told later by a DBA that he had seen this same problem before.

In any event, for SQL Server 2008, these are the steps I took to get this resolved and they worked, so I'm posting this here in the hopes it will be a help to someone. This is what I did as I had FK dependencies on another table that made this more difficult:

I used this query to verify the identity was indeed missing and to view dependencies on the table.

1.) Find statistics on a table:

exec sp_help 'dbo.table_name_old';

2.) Create a duplicate, identical new table, except add an identity field on the PK field where it had been before.

3.) Disable the identity to move data.

SET IDENTITY_INSERT dbo.table_name ON 

4.) Transfer the data.

INSERT INTO dbo.table_name_new
(
field1, field2, etc...
)
SELECT 
field1, field2, etc...
FROM 
dbo.table_name_old;

5.) Verify the data is there.

SELECT * FROM dbo.table_name_new

6.) Re-enable the identity.

SET IDENTITY_INSERT ToyRecP.ToyAwards.lkpFile_New OFF

7.) This is the best script I found to get all the FK relationships to verify which table(s) the original table references as dependencies and I came across many, so it is a keeper!

SELECT f.name AS ForeignKey,
   OBJECT_NAME(f.parent_object_id) AS TableName,
   COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ColumnName,
   OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
   COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
   ON f.OBJECT_ID = fc.constraint_object_id
   ORDER BY ReferenceTableName;

8.) Make sure you have all the PK and FK scripts for all the tables involved, before this next step.

9.) You can right-click on each key and script this using SQL Server 2008

10.) Drop the FK(s) from the dependency table(s) using this syntax:

ALTER TABLE [dbo].[table_name] DROP CONSTRAINT [Name_of_FK]

11.) Drop the original table:

DROP TABLE dbo.table_name_old;

13.) These next steps rely on the scripts you created in SQL Server 2008 in step 9.

--Add the PK to the new table.

--Add the FK to the new table.

--Add the FK's back to the dependency table.

14.) Verify everything is correct and complete. I used the GUI to look at the tables.

15.) Rename the new table to the original tables name.

exec sp_RENAME '[Schema_Name.OldTableName]' , '[NewTableName]';

Finally, everything worked!

@Ken.Fukizi 2017-07-31 16:47:36

To modify the identity properties for a column:

  • In Server Explorer, right-click the table with identity properties you want to modify and click Open Table Definition. The table opens in Table Designer.
  • Clear the Allow nulls check box for the column you want to change.
  • In the Column Properties tab, expand the Identity Specification property.
  • Click the grid cell for the Is Identity child property and choose Yes from the drop-down list.
  • Type a value in the Identity Seed cell. This value will be assigned to the first row in the table. The value 1 will be assigned by default.

That's it, and it worked for me

@vamsi_874 2017-06-12 10:37:50

Right click on table name in Object Explorer. You will get some options. Click on 'Design'. A new tab will be opened for this table. You can add Identity constraint here in 'Column Properties'.

@Ajay2707 2017-04-26 09:43:48

As per my current condition, I follow this approach. I want to give identity to a primary table after data inserted via script.

As I want to append identity, so it always start from 1 to End of record count that I want.

--first drop column and add with identity
ALTER TABLE dbo.tblProductPriceList drop column ID 
ALTER TABLE dbo.tblProductPriceList add ID INT IDENTITY(1,1)

--then add primary key to that column (exist option you can ignore)
IF  NOT EXISTS (SELECT * FROM sys.key_constraints  WHERE object_id = OBJECT_ID(N'[dbo].[PK_tblProductPriceList]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblProductPriceList]'))
    ALTER TABLE [tblProductPriceList] ADD PRIMARY KEY (id)
GO

This will create the same primary key column with identity

I used this links : https://blog.sqlauthority.com/2014/10/11/sql-server-add-auto-incremental-identity-column-to-table-after-creating-table/

Add primary key to existing table

@marc_s 2009-06-26 13:54:23

You cannot alter a column to be an IDENTITY column. What you'll need to do is create a new column which is defined as an IDENTITY from the get-go, then drop the old column, and rename the new one to the old name.

ALTER TABLE (yourTable) ADD NewColumn INT IDENTITY(1,1)

ALTER TABLE (yourTable) DROP COLUMN OldColumnName

EXEC sp_rename 'yourTable.NewColumn', 'OldColumnName', 'COLUMN'

Marc

@Constantine Gladky 2013-05-28 13:45:11

It doesn't work if this column is a constraint

@Jenny O'Reilly 2013-10-02 08:50:02

Either the parameter \@objname is ambiguous or the claimed \@objtype (COLUMN) is wrong.

@marc_s 2013-10-02 08:51:17

@JennyO'Reilly: put that into a separate question, and show us the complete command you're using!

@Jenny O'Reilly 2013-10-03 16:21:22

It was the sp_rename procedure that was failing. I found a solution on stackoverflow by searching for the error text. It seems to be some strict syntax rule with brackets, although my table has no special characters in its name whatsoever.

@R K Sharma 2015-01-09 10:22:47

or it could be like : 'ALTER TABLE (yourTable) DROP COLUMN OldColumnName' and 'ALTER TABLE (yourTable) ADD OldColumnName INT IDENTITY(1,1)', why rename :p

@Sathish Chelladurai 2014-12-29 16:13:02

Simple explanation

Rename the existing column using sp_RENAME

EXEC sp_RENAME 'Table_Name.Existing_ColumnName' , 'New_ColumnName', 'COLUMN'

Example for Rename :

The existing column UserID is renamed as OldUserID

EXEC sp_RENAME 'AdminUsers.UserID' , 'OldUserID', 'COLUMN'

Then add a new column using alter query to set as primary key and identity value

ALTER TABLE TableName ADD Old_ColumnName INT NOT NULL PRIMARY KEY IDENTITY(1,1)

Example for Set Primary key

The new created column name is UserID

ALTER TABLE Users ADD UserID INT NOT NULL PRIMARY KEY IDENTITY(1,1)

then Drop the Renamed Column

ALTER TABLE Table_Name DROP COLUMN Renamed_ColumnName

Example for Drop renamed column

ALTER TABLE Users DROP COLUMN OldUserID

Now we've adding a primarykey and identity to the existing column on the table.

@Kyle Gibbar 2014-10-27 19:24:27

generates a script for all tables with primary key = bigint which do not have an identity set; this will return a list of generated scripts with each table;

SET NOCOUNT ON;

declare @sql table(s varchar(max), id int identity)

DECLARE @table_name nvarchar(max),
        @table_schema nvarchar(max);

DECLARE vendor_cursor CURSOR FOR 
SELECT
  t.name, s.name
FROM sys.schemas AS s
INNER JOIN sys.tables AS t
  ON s.[schema_id] = t.[schema_id]
WHERE EXISTS (
    SELECT
    [c].[name]
    from sys.columns [c]
    join sys.types [y] on [y].system_type_id = [c].system_type_id
    where [c].[object_id] = [t].[object_id] and [y].name = 'bigint' and [c].[column_id] = 1
) and NOT EXISTS 
(
  SELECT 1 FROM sys.identity_columns
    WHERE [object_id] = t.[object_id]
) and exists (
    select 1 from sys.indexes as [i] 
    inner join sys.index_columns as [ic]  ON  i.OBJECT_ID = ic.OBJECT_ID AND i.index_id = ic.index_id
    where object_name([ic].[object_id]) = [t].[name]
)
OPEN vendor_cursor

FETCH NEXT FROM vendor_cursor 
INTO @table_name, @table_schema

WHILE @@FETCH_STATUS = 0
BEGIN

DELETE FROM @sql

declare @pkname varchar(100),
    @pkcol nvarchar(100)

SELECT  top 1
        @pkname = i.name,
        @pkcol = COL_NAME(ic.OBJECT_ID,ic.column_id)
FROM    sys.indexes AS [i]
INNER JOIN sys.index_columns AS [ic] ON  i.OBJECT_ID = ic.OBJECT_ID AND i.index_id = ic.index_id
WHERE   i.is_primary_key = 1 and OBJECT_NAME(ic.OBJECT_ID) = @table_name

declare @q nvarchar(max) = 'SELECT  '[email protected]+' FROM ['[email protected]_schema+'].['[email protected]_name+'] ORDER BY '[email protected]+' DESC'

DECLARE @ident_seed nvarchar(max) -- Change this to the datatype that you are after
SET @q = REPLACE(@q, 'SELECT', 'SELECT TOP 1 @output = ')
EXEC sp_executeSql @q, N'@output bigint OUTPUT', @ident_seed OUTPUT

insert into  @sql(s) values ('BEGIN TRANSACTION')
insert into  @sql(s) values ('BEGIN TRY')

-- create statement
insert into  @sql(s) values ('create table ['[email protected]_schema+'].[' + @table_name + '_Temp] (')

-- column list
insert into @sql(s) 
select 
    '  ['+[c].[name]+'] ' +
    y.name + 

    (case when [y].[name] like '%varchar' then
    coalesce('('+(case when ([c].[max_length] < 0 or [c].[max_length] >= 1024) then 'max' else cast([c].max_length as varchar) end)+')','')
    else '' end)

     + ' ' +
    case when [c].name = @pkcol then 'IDENTITY(' +COALESCE(@ident_seed, '1')+',1)' else '' end + ' ' +
    ( case when c.is_nullable = 0 then 'NOT ' else '' end ) + 'NULL ' + 
    coalesce('DEFAULT ('+(
        REPLACE(
            REPLACE(
                LTrim(
                    RTrim(
                        REPLACE(
                            REPLACE(
                                REPLACE(
                                    REPLACE(
                                        LTrim(
                                            RTrim(
                                                REPLACE(
                                                    REPLACE(
                                                        object_definition([c].default_object_id)
                                                    ,' ','~')
                                                ,')',' ')
                                            )
                                        )
                                    ,' ','*')
                                ,'~',' ')
                            ,' ','~')
                        ,'(',' ')
                    )
                )
            ,' ','*')
        ,'~',' ')
    ) +
    case when object_definition([c].default_object_id) like '%get%date%' then '()' else '' end
    +
    ')','') + ','
 from sys.columns c
 JOIN sys.types y ON y.system_type_id = c.system_type_id
  where OBJECT_NAME(c.[object_id]) = @table_name and [y].name != 'sysname'
 order by [c].column_id


 update @sql set s=left(s,len(s)-1) where [email protected]@identity

-- closing bracket
insert into @sql(s) values( ')' )

insert into @sql(s) values( 'SET IDENTITY_INSERT ['[email protected]_schema+'].['[email protected]_name+'_Temp] ON')

declare @cols nvarchar(max)
SELECT @cols = STUFF(
    (
        select ',['+c.name+']'
        from sys.columns c
        JOIN sys.types y ON y.system_type_id = c.system_type_id
        where c.[object_id] = OBJECT_ID(@table_name)
        and [y].name != 'sysname'
        and [y].name != 'timestamp'
        order by [c].column_id
        FOR XML PATH ('')
     )
    , 1, 1, '')

insert into @sql(s) values( 'IF EXISTS(SELECT * FROM ['[email protected]_schema+'].['[email protected]_name+'])')
insert into @sql(s) values( 'EXEC(''INSERT INTO ['[email protected]_schema+'].['[email protected]_name+'_Temp] ('[email protected]+')')
insert into @sql(s) values( 'SELECT '[email protected]+' FROM ['[email protected]_schema+'].['[email protected]_name+']'')')

insert into @sql(s) values( 'SET IDENTITY_INSERT ['[email protected]_schema+'].['[email protected]_name+'_Temp] OFF')


insert into @sql(s) values( 'DROP TABLE ['[email protected]_schema+'].['[email protected]_name+']')

insert into @sql(s) values( 'EXECUTE sp_rename N''['[email protected]_schema+'].['[email protected]_name+'_Temp]'', N'''[email protected]_name+''', ''OBJECT''')

if ( @pkname is not null ) begin
    insert into @sql(s) values('ALTER TABLE ['[email protected]_schema+'].['[email protected]_name+'] ADD CONSTRAINT ['[email protected]+'] PRIMARY KEY CLUSTERED (')
    insert into @sql(s)
        select '  ['+COLUMN_NAME+'] ASC,' from information_schema.key_column_usage
        where constraint_name = @pkname
        GROUP BY COLUMN_NAME, ordinal_position
        order by ordinal_position

    -- remove trailing comma
    update @sql set s=left(s,len(s)-1) where [email protected]@identity
    insert into @sql(s) values ('  )')
end

insert into  @sql(s) values ('--Run your Statements')
insert into  @sql(s) values ('COMMIT TRANSACTION')
insert into  @sql(s) values ('END TRY')
insert into  @sql(s) values ('BEGIN CATCH')
insert into  @sql(s) values ('        ROLLBACK TRANSACTION')
insert into  @sql(s) values ('        DECLARE @Msg NVARCHAR(MAX)  ')
insert into  @sql(s) values ('        SELECT @Msg=ERROR_MESSAGE() ')
insert into  @sql(s) values ('        RAISERROR(''Error Occured: %s'', 20, 101,@msg) WITH LOG')
insert into  @sql(s) values ('END CATCH')

declare @fqry nvarchar(max)

-- result!
SELECT @fqry = (select char(10) + s from @sql order by id FOR XML PATH (''))


SELECT @table_name as [Table_Name], @fqry as [Generated_Query]
PRINT 'Table: '[email protected]_name
EXEC sp_executeSql @fqry

    FETCH NEXT FROM vendor_cursor 
    INTO @table_name, @table_schema
END 
CLOSE vendor_cursor;
DEALLOCATE vendor_cursor;

@Shivalik Chakravarty 2014-04-19 06:51:31

Basically there are four logical steps.

  1. Create a new Identity column. Turn on Insert Identity for this new column.

  2. Insert the data from the source column (the column you wished to convert to Identity) to this new column.

  3. Turn off the Insert Identity for the new column.

  4. Drop your source column & rename the new column to the name of the source column.

There may be some more complexities like working across multiple servers etc.

Please refer the following article for the steps (using ssms & T-sql). These steps are intended for beginners with less grip on T-SQL.

http://social.technet.microsoft.com/wiki/contents/articles/23816.how-to-convert-int-column-to-identity-in-the-ms-sql-server.aspx

@John Sansom 2009-06-26 13:57:24

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

Approach 1. (New table) Here you can retain the existing data values on the newly created identity column.

CREATE TABLE dbo.Tmp_Names
    (
      Id int NOT NULL
             IDENTITY(1, 1),
      Name varchar(50) NULL
    )
ON  [PRIMARY]
go

SET IDENTITY_INSERT dbo.Tmp_Names ON
go

IF EXISTS ( SELECT  *
            FROM    dbo.Names ) 
    INSERT  INTO dbo.Tmp_Names ( Id, Name )
            SELECT  Id,
                    Name
            FROM    dbo.Names TABLOCKX
go

SET IDENTITY_INSERT dbo.Tmp_Names OFF
go

DROP TABLE dbo.Names
go

Exec sp_rename 'Tmp_Names', 'Names'

Approach 2 (New column) 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'

See the following Microsoft SQL Server Forum post for more details:

How to alter column to identity(1,1)

@Justin Grant 2009-11-13 17:56:44

If table data is small, this option works gret. If table is large, there's another option I prefer: use ALTER TABLE ... SWITCH to replace the table schema with another version with an IDENTITY column but otherwise identical schema. The advantage of the ALTER TABLE.... SWITCH approach is that it completes quickly (under 5 seconds for a billion-row table) since no table data needs to be copied or changed. There are caveats and limitations though. See my answer below for details.

@John Sansom 2009-11-15 12:09:00

@Justin Grat: A very interesting alternative and one that I had not considered! The reason this works is because IDENTITY is a column property and not a data type, so the SWITCH method validates the schemas between the two tables (old and new) as being identifiable irrespective of the IDENTITY difference. Thanks for sharing!

@goamn 2017-06-08 05:05:00

If you don't have much data, then "creating the table" can be achieved by generation a script from SSMS. Right click the table > Scrip Table as > Create TABLE to > (new query editor?). Then drop it, and inside that script you can add the IDENTITY(1, 1) part with the primary key column

@Zafar 2018-05-07 14:17:40

One can also use SSMS to enforce this. Go to Tools > Options > Designers> Un-check "Prevent saving changes that require table re-creation". BTW this is not recommended for fairly large tables.

@Andrew Mackie 2018-08-17 00:25:58

In PostgreSQL you can add identity to an existing integer column with the command: alter table {table_name} alter column {column_name} add generated always as identity (restart with {number});

@Denziloe 2019-02-21 13:47:44

Note that approach 2 will probably change the column order, so that your id, which was probably the leftmost column, is now the rightmost column. This shouldn't matter to any well-coded application, but it may break things if they're not coded so well, and it's disorientating when quickly selecting from a table to preview it.

@greenoldman 2011-08-24 11:06:36

There is cool solution described here: SQL SERVER – Add or Remove Identity Property on Column

In short edit manually your table in SQL Manager, switch the identity, DO NOT SAVE changes, just show the script which will be created for the changes, copy it and use it later.

It is huge time saver, because it (the script) contains all the foreign keys, indices, etc. related to the table you change. Writting this manually... God forbid.

@mdelvecchio 2014-07-06 19:44:51

this is the solution i used -- SSMS generates the T-SQL to make the change...it does so by creating a new temp table of the same schema design, then copying all the rows into it, removing the orig, and renaming. can take a little time to run completely but it worked perfectly.

@Zack 2015-07-09 15:47:59

I don't think Pinal Dave is actually saying you need to run the script that you generate, it is just to show what making the change through the UI does for you...

@Martijn van der Jagt 2015-07-27 16:27:52

This scripting faciity in SSMS (on changing the definition of a table) is actually the only correct facility when documenting a partitioned table. The most appropriate location 'task'->'script table' always forget to script the partitioning function!

@A. Omalley 2011-09-26 16:34:03

If the original poster was actually wanting to set an existing column to be a PRIMARY KEY for the table and actually did not need the column to be an IDENTITY column (two different things) then this can be done via t-SQL with:

ALTER TABLE [YourTableName]
ADD CONSTRAINT [ColumnToSetAsPrimaryKey] PRIMARY KEY ([ColumnToSetAsPrimaryKey])

Note the parenthesis around the column name after the PRIMARY KEY option.

Although this post is old and I am making an assumption about the requestors need, I felt this additional information could be helpful to users encountering this thread as I believe the conversation could lead one to believe that an existing column can not be set to be a primary key without adding it as a new column first which would be incorrect.

@SQLMenace 2009-06-26 14:06:27

you can't do it like that, you need to add another column, drop the original column and rename the new column or or create a new table, copy the data in and drop the old table followed by renaming the new table to the old table

if you use SSMS and set the identity property to ON in the designer here is what SQL Server does behind the scenes. So if you have a table named [user] this is what happens if you make UserID and identity

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
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

GO
CREATE TABLE dbo.Tmp_User
    (
    UserID int NOT NULL IDENTITY (1, 1),
    LastName varchar(50) NOT NULL,
    FirstName varchar(50) NOT NULL,
    MiddleInitial char(1) NULL

    )  ON [PRIMARY]
GO

SET IDENTITY_INSERT dbo.Tmp_User ON
GO
IF EXISTS(SELECT * FROM dbo.[User])
 EXEC('INSERT INTO dbo.Tmp_User (UserID, LastName, FirstName, MiddleInitial)
    SELECT UserID, LastName, FirstName, MiddleInitialFROM dbo.[User] TABLOCKX')
GO
SET IDENTITY_INSERT dbo.Tmp_User OFF
GO

GO
DROP TABLE dbo.[User]
GO
EXECUTE sp_rename N'dbo.Tmp_User', N'User', 'OBJECT'
GO
ALTER TABLE dbo.[User] ADD CONSTRAINT
    PK_User PRIMARY KEY CLUSTERED 
    (
    UserID
    ) ON [PRIMARY]

GO
COMMIT

Having said that there is a way to hack the system table to accomplish it by setting the bitwise value but that is not supported and I wouldn't do it

@Raj 2009-06-26 14:00:39

By design there is no simple way to turn on or turn off the identity feature for an existing column. The only clean way to do this is to create a new column and make it an identity column or create a new table and migrate your data.

If we use SQL Server Management Studio to get rid of the identity value on column "id", a new temporary table is created, the data is moved to the temporary table, the old table is dropped and the new table is renamed.

Use Management Studio to make the change and then right click in the designer and select "Generate Change Script".

You will see that this is what SQL server in doing in the background.

@Jeremy Smyth 2009-06-26 13:57:06

There isn't one, sadly; the IDENTITY property belongs to the table rather than the column.

The easier way is to do it in the GUI, but if this isn't an option, you can go the long way around of copying the data, dropping the column, re-adding it with identity, and putting the data back.

See here for a blow-by-blow account.

@William Edmondson 2009-06-26 13:56:58

I don't believe you can alter an existing column to be an identity column using tsql. However, you can do it through the Enterprise Manager design view.

Alternatively you could create a new row as the identity column, drop the old column, then rename your new column.

ALTER TABLE FooTable
ADD BarColumn INT IDENTITY(1, 1)
               NOT NULL
               PRIMARY KEY CLUSTERED

@Scott Ivey 2009-06-26 14:00:55

keep in mind that if you do it thru SSMS/Enterprise Manager - you'll be creating a new table, copying data, dropping the old table, and renaming the new one. That can be quite expensive when you have large tables...

Related Questions

Sponsored Content

25 Answered Questions

13 Answered Questions

[SOLVED] Best way to get identity of inserted row?

  • 2008-09-03 21:32:02
  • Oded
  • 737082 View
  • 980 Score
  • 13 Answer
  • Tags:   sql sql-server tsql

18 Answered Questions

12 Answered Questions

[SOLVED] Altering a column: null to not null

21 Answered Questions

[SOLVED] What's the best practice for primary keys in tables?

37 Answered Questions

10 Answered Questions

[SOLVED] Rename column SQL Server 2008

19 Answered Questions

15 Answered Questions

[SOLVED] Select n random rows from SQL Server table

  • 2009-05-11 16:19:11
  • John M Gant
  • 322073 View
  • 279 Score
  • 15 Answer
  • Tags:   sql sql-server random

8 Answered Questions

Sponsored Content