Changing identity column from INT to BIGINT

Question

I have a table with an identity column that is also a primary key. Currently, it has 50 million rows, with the highest value of the identity column sitting at 148,921,803. The table has a lot of DELETEs and INSERTS performed on it, hence the high value.

We want to change the data type from INT to BIGINT to prepare for the addition of more rows. Note that, there are no references to the PK column.

What is the best way to do this, with minimal downtime? I have two options.

1. Drop the PK and alter the column; or
2. The copy-drop-rename method, as described here:

Answer 1

As there is a primary key defined on identity column you wont be able to directly alter this column.

Both the approaches that you have mentioned in your question can be used and downtime depends on how your server is performing and number of rows reside in that table.

1. Drop the PK and alter the column; or

First drop the PK

/****** Object: DROP Index [PK_DatabaseLog_DatabaseLogID]******/

ALTER TABLE [dbo].[TableName] DROP CONSTRAINT [PK_TableName_ID]
GO

Alter Column

ALTER TABLE [dbo].[TableName] ALTER COLUMN [dbo.ID] BIGINT

Add Primary key

/****** Object: ADD Index [PK_DatabaseLog_DatabaseLogID]******/
ALTER TABLE [dbo].[TableName] ADD  CONSTRAINT [PK_TableName_ID] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)

This approach usually does not take much time. In my environment it takes mare seconds on big tables which have more than 5 million rows.

2. The copy-drop-rename method, as described

You can use this approach as well. However, for this approach you need more downtime than Approach one as you have to sync the tables.

Answer 2

Aaron Bertrand has a 4-part series on this topic, starting with:

Minimizing impact of widening an IDENTITY column – part 1

If you absolutely need to move to bigint, must minimize downtime, and have plenty of time for planning, the approach he documents in part 4 is:

At a very high level, the approach is to create a set of shadow tables, where all the inserts are directed to a new copy of the table (with the larger data type), and the existence of the two sets of tables is as transparent as possible to the application and its users.

In more detail, Aaron says:

1. Create shadow copies of the tables, with the right data types.
2. Alter the stored procedures (or ad hoc code) to use bigint for parameters. (This may require modification beyond the parameter list, such as local variables, temp tables, etc., but this is not the case here.)
3. Rename the old tables, and create views with those names that union the old and new tables.
   • Those views will have instead of triggers to properly direct DML operations to the appropriate table(s), so that data can still be modified during the migration.
   • This also requires SCHEMABINDING to be dropped from any indexed views, existing views to have unions between new and old tables, and procedures relying on SCOPE_IDENTITY() to be modified.
4. Migrate the old data to the new tables in chunks.
5. Clean up, consisting of:
   • Dropping the temporary views (which will drop the INSTEAD OF triggers).
   • Renaming the new tables back to the original names.
   • Fixing the stored procedures to revert to SCOPE_IDENTITY().
   • Dropping the old, now-empty tables.
   • Putting SCHEMABINDING back on indexed views and re-creating clustered indexes.

Answer 3

Another option is to set the identity seed to -1 (negative one). This will then start creating rows with a negative Id.

Note that I am NOT saying this is a GOOD idea (in fact, it is a bad idea). But in a pinch this will work until a proper solution can be implemented.

Answer 4

If you are able to schedule downtime for your application, the following method worked for our mysql conversion. Because we had many tables with FK constraints, we felt like altering tables, removing FKs, adding back FK constraints would have been more error prone. This method felt very straightforward, and because we were able to schedule downtime, and the export file for us was manageable (<10GB), the process was accomplished in less than 30 minutes in our production environment.

• Export the database to dump.sql
• Perform a search replace in the dump file for all ids (luckily for us the ids regex was quite simple using sed.
• sed -i 's/``\(\w*Id\w*\)`` int/``\1`` bigint/g' dump.sql to replace patterns like TeamId or AppUserIdOwner
• Recreate the database using the modified dump.sql