Home Dashboard Directory Help
Search

ALTER COLUMN IDENTITY by Jason Kresowaty


Status: 

Active


23
1
Sign in
to vote
Type: Suggestion
ID: 294193
Opened: 8/20/2007 7:34:55 AM
Access Restriction: Public
Primary Feedback Item: 252226
0
Workaround(s)
view

Description

Provide an ALTER COLUMN syntax to add or remove the IDENTITY property of a column. This will allow an IDENTITY to be changed without dropping and recreating the table.

This is not only a convenience but is actually important for some scenarios:
1. Suppose the SELECT INTO was used to create the table. SELECT INTO is fast, but subsequently recreating the table just to setup the IDENTITY negates a lot of SELECT INTO's speed.

2. If the table is involved in replication, it is not possible to drop and recreate it.

A similar suggestion in Microsoft Connect involves the ability to MOVE columns, see 124781.
Details
Sign in to post a comment.
Posted by Martin Smith on 8/18/2011 at 7:53 AM
@Michael_Søndergaard See SQLKiwi's workaround on the duplicate connect item that does allow this to be done as a metadata only change.
Posted by Michael_Søndergaard on 4/10/2011 at 3:22 PM
I hope this will get into Denali, because I just had to drop an identity column on a 800 mil table and after executing for 6 hours that was no fun. Now with the introduction of sequences in the next version, this problem will be a major issue, when people starts converting to sequences.

This operation should just be a metadata change, and it shouldn't involve the need to drop & create
Posted by Microsoft on 3/10/2011 at 4:21 PM
Hi,
I have resolved your request as duplicate of one below:

http://connect.microsoft.com/SQLServer/feedback/details/252226/allow-enabling-and-disabling-of-a-columns-identity-property

--
Umachandar, SQL Programmability Team
Posted by Martin Smith on 1/1/2011 at 2:09 PM
Any update on this? It seems pretty ridiculous that the only way of changing a column to be an identity column is either to add a new column and drop the existing one (necessitating an update to every row in the table, wasting space from the dropped column and changing the column order) or to completely rebuild the table. Surely it should be possible to make this meta data change without us having to perform actions that affect the data pages at all.
Posted by Microsoft on 1/28/2008 at 6:28 PM
Hello

Thank you for your feedback. The ability to modify the identity property of an existing column will certainly benefit the scenarios you've described. We'll look into ways of enabling these scenarios in a future release of SQL Server.


-- SQL Server Engine Team
Posted by Microsoft on 1/28/2008 at 6:27 PM
Hello

Thank you for your feedback. The ability to modify the identity property of an existing column will certainly benefit the scenarios you've described. We'll look into ways of enabling these scenarios in a future release of SQL Server.


-- SQL Server Engine Team
Sign in to post a workaround.