Changing identity column from INT to BIGINT

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.

• Drop the PK and alter the column; or

• The copy-drop-rename method, as described here:

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.

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

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

StackExchange Database Administrators Q#159229, answer score: 17