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
更多推荐
发布评论