Post reply

How can An Identity be removed from a Column?

  • January 5, 2005 at 3:05 am

    #61211

    I have atable X with column Y Indentity(1,1). How can I removed identity from table X column Y using T-SQL?

    I don't want to drop table X.

     

  • January 5, 2005 at 3:21 am

    #535935

    alter table X drop column Y

    Hope this helps,

    Kathi

    Aunt Kathi Data Platform MVP
    Author of Expert T-SQL Window Functions
    Simple-Talk Editor

  • January 5, 2005 at 3:29 am

    #535936

    Assuming you want to keep the already existing data, your best bet is to use EM and remove the IDENTITY property there in table design view. Instead of clicking on Save, you might want to have a change script generated, to see what's going on behind the scenes. There's no direct way to drop this property via simple commands.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • January 5, 2005 at 4:57 am

    #535949

    I completely misunderstood the question.  Sorry about that.

     

    Kathi

    Aunt Kathi Data Platform MVP
    Author of Expert T-SQL Window Functions
    Simple-Talk Editor

  • January 5, 2005 at 5:33 am

    #535951

    Right, but I want to prepare a script for production server, so don;t want to do it using EM. Is there any direct method without droping Table and Column?

  • January 5, 2005 at 5:43 am

    #535952

    You can save the change script without the need to apply it to the table. That way you are able to schedule for off-peak hours, as I guess you intend to do.

    Another common method would be to create a new column, update that column with the values in the IDENTITY column, drop the IDENTITY column, and rename the new column with the name of the old IDENTITY. In case you have indexes and/or constraints defined on the IDENTITY column, you need to recreate them, too. Again, it's quite easy to create an IDENTITY column, but it's a royal pain to get rid of it.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • January 5, 2005 at 6:10 am

    #535960

    I think your idea can help. Thanks! Frank.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply