Do you have a comment or suggestion to improve SQL Server? We’d love to hear it!

ALTER COLUMN IDENTITY

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.

4 votes
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    Signed in as (Sign out)

    We’ll send you updates on this idea

    AdminMicrosoft SQL Server (Admin, Microsoft Azure) shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →
    unplanned  ·  AdminMicrosoft SQL Server (Admin, Microsoft Azure) responded  · 

    Upvotes: 27

    <=-=Jan 28 2008 6:27PM=-=>

    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

    <=-=Jan 28 2008 6:28PM=-=>

    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

    <=-=Jan 1 2011 2:09PM=-=>

    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.

    <=-=Mar 10 2011 4:21PM=-=>

    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

    <=-=Apr 10 2011 3:22PM=-=>

    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

    <=-=Aug 18 2011 7:53AM=-=>

    @Michael_S�ndergaard See SQLKiwi’s workaround on the duplicate connect item that does allow this to be done as a metadata only change.

    1 comment

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      Signed in as (Sign out)
      Submitting...
      • Mike Doerfler commented  ·   ·  Flag as inappropriate

        please consider doing this. The work arounds are painful on systems with many tables and many fks. This seems like it should be a simple operation to remove IDENTITY from column as it appears to be metadata only. But must not be if there is not a simple way of accomplishing it.

      Feedback and Knowledge Base