title: Convert INT to BIGINT in SQL Server
body: <p>SQL 2005, 600,000,000 rows.</p>
  
  <p>I have a table called Location currently using the data type INT in identity PK column LocationID. I would like to attempt converting this data type to BIGINT.</p>
  
  <p>The following script I think should help to allow inserted into the PK column but i am unsure how to progress form here.</p>
  
  <pre><code>SET IDENTITY_INSERT LOCATION ON /*allows insert into the identity column*/`
  
  SET IDENTITY_INSERT LOCATION OFF /*Returns the identity column to initial state*/`
  </code></pre>
  
  <p>Location table create script below:</p>
  
  <pre><code>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]
  </code></pre>
  
  <p>One option i think would be to copy the data to a new table then delete the old table and rename the new one however we have constraints that will need to be dropped for this to work.</p>