How do I add the identity property to an existing column in SQL Server

Question

In SQL Server (in my case, 2005) how can I add the identity property to an existing table column using T-SQL?

Something like:

alter table tblFoo 
    alter column bar identity(1,1)

Funny... SQL Server 2005 Compact edition have this syntax but not the server edition... — Hapkido
– Hapkido, Commented Nov 14, 2008 at 12:47
Bounty will go to the TSQL script that can do this. ( sig: spSetIdentity('TableName', 'Id') ) — Sam Saffron
– Sam Saffron, Commented Dec 9, 2010 at 0:15
@Martin, sure 2008 is a valid requirement (even though the question was originally 2005) — Sam Saffron
– Sam Saffron, Commented Dec 9, 2010 at 2:09
If you are on a SKU that supports partitioning this is possible as a meta data only change. — Martin Smith
– Martin Smith, Commented Oct 14, 2011 at 19:56

JohnFx · Accepted Answer · 2008-11-13 20:59:46Z

31

I don't beleive you can do that. Your best bet is to create a new identity column and copy the data over using an identity insert command (if you indeed want to keep the old values).

Here is a decent article describing the process in detail: http://www.mssqltips.com/tip.asp?tip=1397

answered Nov 13, 2008 at 20:59

JohnFx

35k19 gold badges108 silver badges169 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Brent Ozar Over a year ago

You're correct, that isn't possible with the current versions of SQL Server.

Sam Saffron Over a year ago

@Brent, A side effect here is that the column order will change with this trick

davek Over a year ago

column order is only an issue if you are doing "SELECT *" which I seem to recall is/is-going-to-be classified as a warning by SQL Server diagnostic tools (can't remember which one).

Stefan Egli Over a year ago

@Sam, what is the bounty for? keeping the order of the columns? if so you need to create a temporary table, copy all data into it, then re-create the table with correct column ordering and copy the data back. Management Studio can do this for you, but that is only good if you do not a script. Visual Studio DB Edition can create a change script that takes care of re-ordering the columns...

Sam Saffron Over a year ago

@Sefen ... yes, the bounty is for a TSQL script that preserves order and simulates what management studio does.

|

NateJ · Accepted Answer · 2009-05-26 21:14:21Z

12

The solution posted by Vikash doesn't work; it produces an "Incorrect syntax" error in SQL Management Studio (2005, as the OP specified). The fact that the "Compact Edition" of SQL Server supports this kind of operation is just a shortcut, because the real process is more like what Robert & JohnFX said--creating a duplicate table, populating the data, renaming the original & new tables appropriately.

If you want to keep the values that already exist in the field that needs to be an identity, you could do something like this:

CREATE TABLE tname2 (etc.)
INSERT INTO tname2 FROM tname1

DROP TABLE tname1
CREATE TABLE tname1 (with IDENTITY specified)

SET IDENTITY_INSERT tname1 ON
INSERT INTO tname1 FROM tname2
SET IDENTITY_INSERT tname1 OFF

DROP tname2

Of course, dropping and re-creating a table (tname1) that is used by live code is NOT recommended! :)

answered May 26, 2009 at 21:14

NateJ

2,0151 gold badge27 silver badges34 bronze badges

2 Comments

Larry Smithmier Over a year ago

This is also what is required for working with Azure SQL tables.

Mhmd Over a year ago

first creating another table is the only way because if you tried to create another column and rename it, you can not update identity column with old data, Thanks

Robert · Accepted Answer · 2008-11-14 12:08:36Z

2

Is the table populated? If not drop and recreate the table.

If it is populated what values already exist in the column? If they are values you don't want to keep.

Create a new table as you desire it, load the records from your old table into your new talbe and let the database populate the identity column as normal. Rename your original table and rename the new one to the correct name :).

Finally if the column you wish to make identity currently contains primary key values and is being referenced already by other tables you will need to totally re think if you're sure this is what you want to do :)

answered Nov 14, 2008 at 12:08

Robert

1,8454 gold badges25 silver badges30 bronze badges

Comments

Milan · Accepted Answer · 2017-01-25 19:50:45Z

There is no direct way of doing this except:

A) through SQL i.e.:

-- make sure you have the correct CREATE TABLE script ready with IDENTITY

SELECT * INTO abcTable_copy FROM abcTable

DROP TABLE abcTable

CREATE TABLE abcTable -- this time with the IDENTITY column

SET IDENTITY_INSERT abcTable ON

INSERT INTO abcTable (..specify all columns!) FROM (..specify all columns!) abcTable_copy

SET INDENTITY_INSERT abcTable OFF

DROP TABLE abcTable_copy 

-- I would suggest to verify the contents of both tables 
-- before dropping the copy table

B) Through MSSMS which will do exactly the same in the background but will less fat-fingering.

In the MSSMS Object Explorer right click the table you need to modify
Select "design" Select the column you'd like to add IDENTITY to
Change the identity setting from NO -> YES (possibly seed)
Ctr+S the table

This will drop and recreate the table with all original data in it. If you get a warning:

Go to MSSMS Tools -> Options -> Designers -> Table and database Designers and uncheck the option "Prevent saving changes that require table re-creation"

Things to be careful about:

your DB has enough disk space before you do this
the DB is not in use (especially the table you are changing)
make sure to backup your DB before doing it
if the table has a lot of data (over 1G) try it somewhere else first before using in real DB

barbsan · Accepted Answer · 2019-07-04 10:39:47Z

2

Create a New Table

SELECT * INTO Table_New FROM Table_Current WHERE 1 = 0;

Drop Column from New Table
```
Alter table Table_New drop column id;
```

Add column with identity

Alter table Table_New add id int primary key identity;

Get All Data in New Table

SET IDENTITY_INSERT Table_New ON;
INSERT INTO Table_New (id, Name,CreatedDate,Modified) 
SELECT id, Name,CreatedDate,Modified FROM Table_Current;
SET IDENTITY_INSERT Table_New OFF;

Drop old Table
```
drop table Table_Current;
```

Rename New Table as old One

EXEC sp_rename 'Table_New', 'Table_Current';

edited Jul 4, 2019 at 10:39

barbsan

3,46811 gold badges23 silver badges29 bronze badges

answered Jul 4, 2019 at 10:16

Anuj Kumar

211 bronze badge

Comments

a_m0d · Accepted Answer · 2011-10-14 19:51:16Z

-5

alter table tablename 
alter column columnname 
add Identity(100,1)

edited Oct 14, 2011 at 19:51

a_m0d

12.2k15 gold badges60 silver badges81 bronze badges

answered Feb 26, 2009 at 15:06

Vikash

2 Comments

Jim Counts Over a year ago

This works where you column MAX is 100. The first argument should be a value = to the MAX in your column.

Greg Over a year ago

This syntax doesn't work for SQL Server 2008 R2 either.

Collectives™ on Stack Overflow

How do I add the identity property to an existing column in SQL Server

6 Answers 6

9 Comments

2 Comments

Comments

Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

9 Comments

2 Comments

Comments

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related