SQL Server Performance

Create Auto Increment

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by chris_w, Feb 1, 2007.

  1. chris_w New Member

    Hi I found this site and was hoping somebody could help me out.

    I've got a large table (about 500,000+ rows), and I need to set the primary key to autoincrement, the problem is when I attempt this in the management studio it time-outs.

    So my next attempt was to create a new table with the same columns but set the PK to autoincrement, and then do a insert into 'blah' select * from 'original blah'. This worked fairly well, but still took 20+ minutes to copy the data into the new table.

    Has anybody got any ideas on how to reduce the time it takes to copy data between the two tables? Or any other ideas for that matter, I've tried to look into bulk inserts, but I'm not sure if that will help.

    Any help would be greatly appreciated, thanks!
  2. thomas New Member

    - make sure you have enough free space in the database before you populate the new table. If the database has to grow, it'll take longer.
    - create the new table with no indexes on it. Create the indexes afterwards

    Are you adding a new column with the identity property? i.e. a new PK?
  3. MohammedU New Member

    Make sure no one is inserting the data into the old table while populating the new table
    OR
    Copy new rows from old table to the new table after populating the new table...
    Otherwise you may loose/miss some data...

    Use SELECT INTO option OR use DTS with fast load enabled to load the data into new table and make sure indexes on the new table...

    as Thomas mentioned create the indexes after populating the data...


    MohammedU.
    Moderator
    SQL-Server-Performance.com
  4. satya Moderator

    If you can schedule this dtS task as a job then it might have better performance than running from query analyzer, at the same time its better to run during less traffic hours on database for performance aspect (if it is an ongoing process).

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  5. chris_w New Member

    Thanks for everyone's replies.
  6. chris_w New Member

    quote:Originally posted by thomas
    Are you adding a new column with the identity property? i.e. a new PK?

    The current PK doesn't autoincrement, it finds the PK id of the last record and +1 then inserts a new record. I need to change the PK so it uses identity, but with making sure that it doesn't affect the 500,000+ records that already exist.


    quote:Originally posted by thomas
    - create the new table with no indexes on it. Create the indexes afterwards

    It took 22 minutes to populate the new table with no indexes, I need it to populate faster.


    quote:Originally posted by MohammedU
    Make sure no one is inserting the data into the old table while populating the new table
    OR
    Copy new rows from old table to the new table after populating the new table...
    Otherwise you may loose/miss some data...

    I will also have to make sure nobody can update exisiting rows if I use a new table! So if my only choice is to add the identity column by creating a new table I will have to take the site offline, which I was ideally hoping to avoid.


    quote:Originally posted by satya
    If you can schedule this dtS task as a job then it might have better performance than running from query analyzer, at the same time its better to run during less traffic hours on database for performance aspect (if it is an ongoing process).

    The 22 minutes I mentioned was with a scheduled job at the least busy time of my website, I want to limit the time the site is down if I have no other choice.

    I can create a new column on the existing table with identity set, is there a way to update the field with it's PK ID value using something similar to identity insert? Although I feel this will take a very long time to execute.

    I really appreciate the help guys.
  7. happycat59 New Member

    You can do what you want by using IDENTITY_INSERT

    For example (i have left the primary key definition out to make the code a bit easier to read.)
    Create oldtable
    (OldPrimaryKey int, -- this is your current manually incrementing column
    OtherColumn1 char(1),
    OtherColumn2 char(1),
    OtherColumn3 char(1))
    go
    Create newtable
    (NewPrimaryKey int identity (1, 1),
    OtherColumn1 char(1),
    OtherColumn2 char(1),
    OtherColumn3 char(1))

    go
    Set identity_insert newtable on
    insert into newtable (newprimarykey, OtherColumn1, OtherColumn2, OtherColumn3)
    Select OldPrimaryKey, OtherColumn1, OtherColumn2, OtherColumn3
    from oldtable

    set identity_insert newtable off
    go
    sp_rename oldtable, oldtable_save
    go
    sp_rename newtable, oldtable
    go



  8. chris_w New Member

    I did do this. My request was about speeding up this process.

    THanks anyway. I have come to the conclusion that I have to just live with it taking this long.

    I appreciate everyone's help.

  9. mmarovic Active Member

    You could speed-up the query using nolock hint, but you would have to indentify updated/deleted rows later and update new table.

Share This Page