SQL SERVER – ALTER Column from INT to BIGINT – Error and Solutions

Sometime ago I wrote a blog on identity column value jump issue, you can read it from here SQL SERVER –  Jump in Identity column after restart! In this blog post we will learn how to resolve the error when we alter column from int to bigint.

One of my clients contacted me for consultation and they have exhausted Integer range. Due to this, they wanted to know the possible ways to change the existing column to bigint data type.

SOLUTION/WORKAROUND

If there was no primary key on this table, we could have used ALTER TABLE… ALTER COLUMN syntax to modify the data type like below.

ALTER TABLE OneTable ALTER COLUMN ID bigint

In case of primary key or FK dependency, it would fail with below error:

Msg 5074, Level 16, State 1, Line 1
The object ‘PK_OneTable’ is dependent on column ‘ID’.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN ID failed because one or more objects access this column.

You can refer https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-table-transact-sql for more detailed syntax and limitations.

Here are the solutions which I have shared with them.

Solution 1

1. Create a new bigint column in the table
2. Update that new column with the values from the int column
3. Delete the int column
4. Rename the bigint column

Here is the sample:

CREATE TABLE [dbo].[OneTable](
	[ID] [int] NOT NULL PRIMARY KEY,
	[FName] [nchar](10) NULL,
	[Lname] [nchar](10) NULL,
)
GO

ALTER TABLE OneTable ADD NewColumn BIGINT NOT NULL
GO
UPDATE OneTable SET NewColumn=ID
GO
ALTER TABLE [dbo].[OneTable] DROP CONSTRAINT [PK_OneTable] WITH ( ONLINE = ON )
GO
ALTER TABLE OneTable DROP COLUMN ID
GO
USE [Foo]
GO
ALTER TABLE [dbo].[OneTable] ADD  CONSTRAINT [PK_OneTable] PRIMARY KEY CLUSTERED 
(
	[NewColumn] ASC
)
GO

EXEC sp_rename 'OneTable.NewColumn', 'ID', 'COLUMN'

This approach has a problem that column order would change, and the application might break, if it depends on the column order. Honestly, you should not write an application which depends on the column order. You can read it here: How to Add Column at Specific Location in Table? – Interview Question of the Week #126

Here is  the output, which we get when we run sp_rename

Caution: Changing any part of an object name could break scripts and stored procedures.

 Solution 2

1. Created a new table with new datatype.
2. Move data to the new table
3. Drop old table
4. Renamed new table

Both solutions need downtime so need to be done during the maintenance window.

Do you have any other solution? Please comment and share with other readers.

Reference: Pinal Dave (https://blog.sqlauthority.com)