在SQL Server中将INT转换为BIGINT(Convert INT to BIGINT in SQL Server)

SQL 2005,600,000,000行。

我有一个名为Location的表，当前在标识PK列LocationID中使用数据类型INT。我想尝试将此数据类型转换为BIGINT。

我认为以下脚本应该有助于允许插入PK列，但我不确定如何在此处进行表单。

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

位置表创建脚本如下：

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]

我认为一种选择是将数据复制到新表然后删除旧表并重命名新表，但是我们有需要删除的约束才能使其工作。

SQL 2005, 600,000,000 rows.

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.

The following script I think should help to allow inserted into the PK column but i am unsure how to progress form 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 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 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.

最满意答案

您对新桌子的想法是可行的方法。

在开发服务器上，如果使用表设计器更改数据类型，则可以看到SSMS将生成的脚本。这是一个好的开始。这将在之后添加触发器和约束。

像Red gate SQL Compare这样的工具还允许您检查所有内容是否都已创建

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

On a development server, you can see the script that SSMS would produce if you change the data type using the table designer. It is a good start. This will add triggers and constraints back afterwards.

A tool like Red gate SQL Compare also allows you to check that everything was created OK

更多推荐