Sql-server – Fast Conversion to Big INT

sql serversql server 2014type conversion

I have a huge number of large databases (300+!) that vary in size from teeny tiny to absolutely massive (120 GB+) in size. I've already determined a way to generate scripts to decide what indexes, foreign keys, primary keys, and default constraints need to be removed in order to convert a known set of columns from int to bigint data types.

The problem we're running into is on the larger databases, we're hitting 2-3 days runtime on this query, which isn't acceptable in production. Is there a method to make these conversions that is faster than the current drop-change-rebuild process?

Best Answer

I had a similar conversion of a key column from int to bigint that involved hundreds of tables, some over 100GB. I used an approach like the one below, which accomplished the migration a couple of hours against the large client databases. If the database is in the FULL recovery model, make sure sufficient transaction log space exists and/or perform log backups during the migration to avoid filling the log.

1) Drop referencing foreign keys on tables to be modified and schemabound objects

2) Optionally, drop all non-clustered indexes (including non-clustered primary key and non-clustered unique constraint indexes) on tables to be modified to reduce disk space requirements during the migration.

3) For each table, create new table using SELECT...INTO with an explicit CAST of the column to bigint, with ISNULL to coerce a NOT NULL column when necessary. Drop original table and rename new table to original name.

4) Recreate clustered indexes/constraints, non-clustered indexes/constraints, followed by other constraints and objects.

Related Question