How To Add An Identity Column To An Existing Database Table Which Has Large Number Of Rows

I have a database table which has ~ 40 000 000 rows. I want to add an identity column to this table. How to do it in a log-friendly manner? When I do the following: ALTER TABLE tab

Solution 1:

The overall process will probably be a lot slower with more overall locking overhead but if you only care about transaction log size you could try the following.

1. Add a nullable integer non identity column (metadata only change).
2. Write code to update this with unique sequential integers in batches. This will reduce the size of each individual transaction and keep the log size down (assuming simple recovery model). My code below does this in batches of 100 hopefully you have an existing PK you can leverage to pick up where you left off rather than the repeated scans that will take increasingly long towards the end.
3. use ALTER TABLE ... ALTER COLUMN to mark the column as NOT NULL. This will require the entire table to be locked and scanned to validate the change but not require much logging.
4. Use ALTER TABLE ... SWITCH to make the column an identity column. This is a metadata only change.

Example Code Below

/*Set up test table with just one column*/CREATETABLE table_1 ( original_column INT )
INSERTINTO table_1
        SELECTDISTINCT
                number
        FROM    master..spt_values



/*Step 1 */ALTERTABLE table_1 ADD id INTNULL/*Step 2 */DECLARE@CounterINT=0 ,
    @PrevCounterINT=-1

WHILE @PrevCounter<>@CounterBEGINSET@PrevCounter=@Counter;
        WITH    T AS ( SELECT TOP 100* ,
                                ROW_NUMBER() OVER ( ORDERBY @@SPID )
                                +@CounterAS new_id
                       FROM     table_1
                       WHERE    id ISNULL
                     )
            UPDATE  T
            SET     id = new_id
        SET@Counter=@Counter+ @@ROWCOUNTENDBEGIN TRY;
    BEGIN TRANSACTION ;
     /*Step 3 */ALTERTABLE table_1 ALTERCOLUMN id INTNOTNULL/*Step 4 */DECLARE@TableScript NVARCHAR(MAX) ='
    CREATE TABLE dbo.Destination(
        original_column INT,
        id INT IDENTITY('+CAST(@Counter+1ASVARCHAR) +',1)
        )

        ALTER TABLE dbo.table_1 SWITCH TO dbo.Destination;
    'EXEC(@TableScript)


    DROPTABLE table_1 ;

    EXECUTE sp_rename N'dbo.Destination', N'table_1', 'OBJECT' ;


    COMMIT TRANSACTION ;
END TRY
BEGIN CATCH
    IF XACT_STATE() <>0ROLLBACK TRANSACTION ;
    PRINT ERROR_MESSAGE() ;
END CATCH ;

Solution 2:

There are two ways of adding an identity column to a table with existing data:

1. Create a new table with identity, copy data to this new table then drop the existing table followed by renaming the temp table.

2. Create a new column with identity & drop the existing column

Reference : http://cavemansblog.wordpress.com/2009/04/02/sql-how-to-add-an-identity-column-to-a-table-with-data/

Solution 3:

I just did this to my table that has over 2700 rows. Go to the design of the table, add the new column, set it to not allow nulls, set the column as an identity column in the column properties, and that should do it. I literally just did this less than 5 minutes ago and it worked for me. Please select as answer if this answers your question.