Post reply

Changing IDENTITY column of a huge table from INT to BIGINT - is there a way without recreating the entire table?

  • March 24, 2011 at 12:52 pm

    #234531

    I have a huge database table (close to a billion records) and would like to change my IDENTITY column data type from INT to BIGINT.

    Is there a way to change the data type without having to drop and recreate the table?

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

  • March 25, 2011 at 12:24 am

    #1303367

    Is there a way to change the data type without having to drop and recreate the table?

    Change the column data type using ALTER TABLE command.

    Don't use GUI. GUI would drop and recreate the table.

  • March 25, 2011 at 2:14 am

    #1303397

    If it's a primary key, drop it and all foreign keys that reference it. Then you should be able to just do an ALTER TABLE ... ALTER COLUMN

    That said, a billion rows, it's going to have some major transaction log impact.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 25, 2011 at 2:57 am

    #1303419

    Thanks both for the suggestions. For some reason I thought ALTER TABLE...ALTER COLUMN did not work for IDENTITY columns.

    I must have been thinking of something else.

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

  • March 25, 2011 at 3:07 am

    #1303427

    Err, don't know. Test it.

    Otherwise, add a new bigint column, update it with the identity values, drop the int column then alter the bigint to be identity (iirc that works, but dropping identity doesn't)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 25, 2011 at 3:12 am

    #1303431

    I just tested it, it works.

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

  • May 3, 2012 at 4:26 am

    #1482905

    how much time did it take for a table with billion rows?

Viewing 7 posts - 1 through 6 (of 6 total)

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