Post reply

Setting identy to on

  • December 19, 2007 at 9:28 am

    #377569

    Hi there is a table with 1.5 mil rows the primary key should have had identity on. It was the test so I put it on. 1 hour later and EM is still thinking. Opened up a new instance of EM and current activity is locked. Can anyone give me an explanation to what happened and if there is anyway to turn it on with out massive delays? I honestly thought it wouldn’t update the entries in the table was I wrong?

  • December 19, 2007 at 9:40 am

    #761450

    When you make a schema change, a new table is created, the data copied over, and then the old table dropped, new table renamed.

    This is probably what's happening. If you make a change in SSMS, then use the "Script button" on the toolbar (just hover over them to find it) and see what will be run.

    I would recommend you save this off as a record of the change and then run the script rather than letting SSMS make the change.

  • December 19, 2007 at 9:51 am

    #761462

    Well that explains it. many thanks.

    This leads me to another question the reasion I did this was that i want to archive of some data from 'current' table (10 mil rows)which had indenty on.

    I created a new table with identy of so I could insert 1.5 mil rows from the 'current table'

    I then changed the table name for 'current' to 'archive'

    then changed 'new' to current'

    all going well until I try to enable indenty on the new 'current'

    any ideas?

  • December 20, 2007 at 9:04 am

    #761794

    You set the identity when you create the table. There's no need to turn it on. The inserts will activate it.

Viewing 4 posts - 1 through 3 (of 3 total)

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