SQL Server Performance

A small query

Discussion in 'SQL Server 2005 General Developer Questions' started by Siona, Sep 30, 2009.

  1. Siona New Member

    Hi All,
    I need to alter a existing column to IDENTITY in a table, but it is not happening. I mean in my table "Employee", Employee_ID column is previously INT type but now I want to make it IDENTITY type in sql script. with out using Management Studio, I need a script and I wrote a script but it is showing "ERROR" below.
    Please advice
    Cheers!
    Siona.
    Siona, Sep 30, 2009
    #2

  2. FrankKalis Moderator

    It won't work this way.
    Add another column with the identity property to the table
    Copy over the data from the old column to the new column using identity_insert = ON
    drop the old column
    rename new column to old columns name
    FrankKalis, Sep 30, 2009
    #3

  3. Siona New Member

    But Frank, I have a case where I don't want to insert new column as IDENTITY Column in my table
    Lets say in my Employee_ID is already contains IDENTITY type of Data and more over it has some dependancy with other database table. In this time what you will do?
    If you creat a new column may be data changes and dependancy will go. then....
    Please Suggest.
    Thanks
    Siona.
    Siona, Sep 30, 2009
    #4

  4. FrankKalis Moderator

    Do you mean that you already have an identity column in the table or that you have data that you want to use in the new identity column? You can have only one identity column per table.
    One step I mentioned was to "migrate" the data from the old non-identity column to the new identity column. So, your existing data would be preserved and is not lost. If there are constraints pointing to the old non-identity column they would have to be dropped before you can drop the column and need to be recreated on the new identity column.
    Sorry, but there is no easier way to achieve this.

    FrankKalis, Sep 30, 2009
    #5

  5. FrankKalis Moderator

    Addition: The "data migration" would have to be done using the SET IDENTITY_INSERT <your table> ON setting. This is the only way to insert custom values into an identity column.
    FrankKalis, Sep 30, 2009
    #6

  6. Siona New Member

    Frank, I didn't mean to create two identity columns which are not possible. And when you say Sorry, but there is no easier way to achieve this but there should have any way.. right?
    2nd option is for data migration which i am ok with this option.
    But I asked altering the existing column to make IDENTITY type. Which i am not sure it can be achieved...
    If you suggest any other option would be great.
    Thanks,
    Siona.
    Siona, Sep 30, 2009
    #7

  7. FrankKalis Moderator

    If there would be an easy and straight ALTER TABLE ... ALTER COLUMN... IDENTITY, I would have suggested this, but there isn't.
    Actually you can do this in Management Studio in the design view for the table. But instead of saving the change, let SSMS generate the change script for you. There you can see what it is doing behind the scenes. [:)]
    FrankKalis, Sep 30, 2009
    #8

Share This Page