Convert INT to BIGINT in SQL Server

Question

SQL 2005, 600,000,000 rows.

I have a table called Location currently using the INT data type in PK column LocationID. I would like to try converting this data type to BIGINT.

The following script, I think, should help include the PK inserted in the column, but I'm not sure how to progress here.

SET IDENTITY_INSERT LOCATION ON /*allows insert into the identity column*/` SET IDENTITY_INSERT LOCATION OFF /*Returns the identity column to initial state*/` 

Location table create the script below:

 CREATE TABLE [dbo].[Location]( [LocationID] [int] IDENTITY(1,1) NOT NULL, [JourneyID] [int] NULL, [DeviceID] [int] NOT NULL, [PacketTypeID] [int] NULL, [PacketStatusID] [int] NULL, CONSTRAINT [Location_PK] PRIMARY KEY CLUSTERED ( [LocationID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Location] WITH CHECK ADD CONSTRAINT [Device_Location_FK1] FOREIGN KEY([DeviceID]) REFERENCES [dbo].[Device] ([DeviceID]) GO ALTER TABLE [dbo].[Location] CHECK CONSTRAINT [Device_Location_FK1] GO ALTER TABLE [dbo].[Location] WITH CHECK ADD CONSTRAINT [PacketStatus_Location_FK1] FOREIGN KEY([PacketStatusID]) REFERENCES [dbo].[PacketStatus] ([PacketStatusID]) GO ALTER TABLE [dbo].[Location] CHECK CONSTRAINT [PacketStatus_Location_FK1] GO ALTER TABLE [dbo].[Location] WITH CHECK ADD CONSTRAINT [PacketType_Location_FK1] FOREIGN KEY([PacketTypeID]) REFERENCES [dbo].[PacketType] ([PacketTypeID]) GO ALTER TABLE [dbo].[Location] CHECK CONSTRAINT [PacketType_Location_FK1] 

One option, I think, was to copy the data to a new table, and then delete the old table and rename the new one, but we have limitations that need to be dropped for this.

Answer 1

Your idea for a new table is the way to go.

On the development server, you can see the script that will be generated by SSMS if you change the data type using the table constructor. This is a good start. After that triggers and restrictions will be added.

A tool such as Red gate SQL Compare also allows you to verify that everything was created OK