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:
up vote 4 down vote accepted

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.

  1. 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.

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.

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.