Identity Issue

  • I have a Table where in i have to set Identity in it.... it has 1 Lakh records in it..

    when i go to design mode of the table & try to set the Identity to YES... it gives the below error

    :w00t:

    'Record_1' table

    - Saving Definition Changes to tables with large amounts of data could take a considerable amount of time. While changes are being saved, table data will not be accessible.

    - Warning: One or more existing columns have ANSI_PADDING 'off' and will be re-created with ANSI_PADDING 'on'.

    :w00t:

    Please help

    ************************************
    Every Dog has a Tail !!!!! :-D

  • You can't add an Identity property to an existing field.

    So the way the GUI will do that is:

    Create a new table that does have the column with Identity property

    Copying the data across from old table to new.

    Delete old table

    Rename new table & associated constraints (FK's etc.) to the original names.

    While it's doing this, the tables will be locked so you can't make any data modifications.

    If you hit the Generate Change Script button rather than save, you can see the script that does all of the above.

    The ANSI Padding warning is because the new table will be created with SET ANSI_PADDING ON, whereas some or all of the old table columns were created with it off. Probably a good idea to move to ON (as OFF is being removed from SQL), but check on the difference in behaviour of the two and see if it affects you.

    You can script out & do this manually, which will give you more control on how long the table is unavailable.

Viewing 2 posts - 1 through 1 (of 1 total)

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