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.

SQL SERVER - ALTER Column from INT to BIGINT - Error and Solutions intbigint-800x225

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.

Solarwinds

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)

Solarwinds
, , ,
Previous Post
SQL SERVER – MSDB Database Uncontrolled Growth Due to Queue_messages. How to Clear All Messages From a Queue?
Next Post
SQL SERVER – Running CHECKDB with Minimum Repair Level

Related Posts

3 Comments. Leave new

  • Keith Monroe
    July 4, 2017 2:40 pm

    When the integer range 1 through 2,147,483,647 is exhausted, using negative values may allow more time for restructuring the tables to use bigint.

    For example, if the integer is an identity column then reseeding to -2,147,483,648 allows the insertion of another 2 billion rows before restructuring is required.

    dbcc checkident( OneTable, reseed, -2147483648 )

    Reply
    • Chris Lemmonds
      July 4, 2017 8:54 pm

      I would also prefer to reseed the identity because a PK that maxes out is probably referenced by dozens of foreign keys, each of which would require dropping the constraint and changing the fk column’s data type in the referencing table. Reseeding is much faster than writing a script for dozens of constraints and tables and would avoid the issue for another 2 billion records. I could see management/support griping about unsightly negative keys, but keys are only supposed to be meaningful to the system – not aesthetically pleasing to managers.

      Reply
  • Scott Frigard
    July 11, 2017 10:51 pm

    Any reason dropping the constraints, alter the column and putting the constraints back in place wouldn’t be an option?

    Reply

Leave a Reply

Menu