将INT改为BigInt

Question

我有一个库存表格，其中包含16吨的数据。表格中有一些整数列。由于求和值太大而无法容纳在整数型中，因此我们必须为每个查询将其强制转换为BIGINT类型。

我们正在开发一个新的数据集市。所以我们想，为什么不将所有这些列都更改为BIGINT，这样我们就可以减少对新查询的担忧。

由于数据已经加载完毕，我想使用Management Studio更改数据类型。但我首先收到一个警告： 保存对具有大量数据的表的定义更改可能需要相当长的时间。在保存更改时，表数据将无法访问。 然后我遇到了一个错误： 操作超时。操作完成之前超时时间已过或服务器未响应。 我该如何解决这个问题？

- Raj More

---

16吨的数据 - 这是很多的！ - D'Arcy Rittich

你考虑过使用临时表、新结构、插入和替换吗？ - Adriaan Stander

@astander -- 这本质上就是 SSMS 中的模式更改正在幕后完成的工作。 - tvanfosson

那些吨是公制吨吗？ - Nathan Koop

一个比特有多重呢？;-) - Kevin Francis

Answer 1

如果其中一个或多个列没有约束条件（如外键、索引、默认值、规则等），则可以通过以下方式快速更改每个列：ALTER TABLE monster ALTER COLUMN MyIntCol1 bigint。管理工具的SQL修改很少是最有效的，往往倾向于使用临时表来修改现有列。

- MartW

---

有趣，我不知道管理工作室在那方面效率低下。 - D'Arcy Rittich

2020年的评论：在SQL Server 2016及以上版本中，您可以添加WITH (ONLINE = ON)来防止停机。 - Alex from Jitbit

Answer 2

不确定这是否有帮助，但请尝试以下方法：

1 - create a new bigint column in the table
2 - update that new column with the values from the int column
3 - delete the int column
4 - rename the bigint column

- D'Arcy Rittich

---

我也是，我正在打同样的东西。（+1）我想要补充一下，在复制情况下这也可以工作。 - Tim Santeford

需要注意的是，使用这种技术会改变列的顺序。虽然这应该没问题，但有可能某些人依赖于这个顺序是稳定的，例如当他们选择*并插入到临时表中时。 - eddie.sholl

Answer 3

这个技巧对我非常有效。

我执行了：

use [Mytable]
ALTER TABLE [dbo].[USER] ALTER COLUMN USER_ID bigint NOT NULL

出现此错误是因为该键存在约束：

Msg 5074, Level 16, State 1, Line 2
The object 'PK_USER_USER_ID' is dependent on column 'USER_ID'.
Msg 4922, Level 16, State 9, Line 2
ALTER TABLE ALTER COLUMN USER_ID failed because one or more objects access this column.

为了不被阻挡，我在SQL Server Management Studio中右键单击约束条件PK_USER_USER_ID，然后选择“脚本键为>>删除并创建为>>新查询编辑器窗口”：

这将生成以下脚本：

USE [Database]
GO
/****** Object:  Index [PK_USER_USER_ID]    Script Date: 18/03/2014 13:05:38 ******/
ALTER TABLE [dbo].[USER] DROP CONSTRAINT [PK_USER_USER_ID]
GO

/****** Object:  Index [PK_USER_USER_ID]    Script Date: 18/03/2014 13:05:38 ******/
ALTER TABLE [dbo].[USER] ADD  CONSTRAINT [PK_USER_USER_ID] PRIMARY KEY CLUSTERED 
(
[USER_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF,        ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

我随后执行了该脚本的前半部分，以删除该约束：

ALTER TABLE [dbo].[USER] DROP CONSTRAINT [PK_USER_USER_ID]
GO

现在，限制已经消失，原始的更改起作用了：

use [Mytable]
ALTER TABLE [dbo].[USER] ALTER COLUMN USER_ID bigint NOT NULL

然后我执行了脚本的后半部分，以便重新添加约束条件：

ALTER TABLE [dbo].[USER] ADD  CONSTRAINT [PK_USER_USER_ID] PRIMARY KEY CLUSTERED 
(
[USER_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF,        ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

- Contango

---

我找到了一个简单的解决方案，如果我们有SQL管理工具，在那里我将运行一个带有不同表名、约束名和关键字名的创建和修改命令的 SQL 脚本。然后重命名现有的表。然后将新表重命名为适当的旧表名称。 - Baskar PC

Answer 4

我认为你可能遇到的主要错误是GUI超时。当你使用SSMS的Modify选择应用大的更改时，它会超时。如果你通过在SSMS中生成更改脚本，然后作为纯SQL查询运行相同的命令，它将一直运行直到完成。

- Chris Coneybeer

Answer 5

如果源数据从未超过 INT 限制，只需创建一个将该列升级为 BIGINT 的 VIEW，并针对该列进行查询。

- Hafthor

Answer 6

进一步补充 OrbMan 的回答：

• 在列列表底部添加新的列（这样可以大大提高速度）
• 如果必要，您可以将更新分批进行，每批10,000行左右
• 确保您处于单用户模式，或者该应用程序处于“关闭”状态，以便其他人不会更改该表中的数据

此外，为了查看 Management Studio 在更改表时所做的所有工作，请单击工具栏上看起来像带有磁盘的滚动条的图标。 这将显示用于更改表的实际 SQL 命令。

- KM.

Answer 7

如果您使用的是SQL Server 2016，则我们可以在表和索引（如果有）上启用压缩，然后将其作为元数据操作运行。如果您的表被压缩，则可以充分利用它。

1) 如果是压缩表，则查找任何其他非聚集索引。 2) 您可以使用ROW或page来压缩它们。不需要将压缩方式与基本表相同。 3) 一旦所有非聚集索引都被压缩，那么我们就可以使用Alter命令了。

我在公司中完成了这项工作，并在我的博客中发布了文章。https://www.chiranjeevivamsydba.com/2020/07/how-to-make-datatype-change-to-run-as.html

- Vamsy chiranjeevi

---

请问您能否编辑您的答案，包括您博客文章中描述的步骤摘要？我们要求这里的答案是自包含的，也就是说，即使您的链接失效，它们仍然必须保持有用性。(参见：[回答]) 谢谢！ - Cody Gray

有官方文档吗？这对我们也起作用了。注意，该列不能是任何索引的一部分，否则这将无法工作。 - Gabe

在这里发现了Paul White的一篇文章： https://sqlperformance.com/2020/04/database-design/new-metadata-column-changes-sql-server-2016 - Gabe