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.
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.
- Create a new bigint column in the table
- Update that new column with the values from the int column
- Delete the int column
- 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.
- Created a new table with new datatype.
- Move data to the new table
- Drop old table
- 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)