SQL Server ALTER 字段 NOT NULL 需要很长时间

发布于 2024-07-27 00:21:40 字数 222 浏览 10 评论 0原文

我想更改大约有 400 万条记录的表中的字段。我确保所有这些字段值都不为空，并且想要将此字段更改为非空

ALTER TABLE dbo.MyTable
ALTER COLUMN myColumn int NOT NULL

......似乎需要永远进行此更新。有什么方法可以加快速度，还是我只能在下班时间过夜？

这也可能导致表锁吗？

原文

I want to alter a field from a table which has about 4 million records. I ensured that all of these fields values are NOT NULL and want to ALTER this field to NOT NULL

ALTER TABLE dbo.MyTable
ALTER COLUMN myColumn int NOT NULL

... seems to take forever to do this update. Any ways to speed it up or am I stuck just doing it overnight during off-hours?

Also could this cause a table lock?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

何其悲哀 2024-08-03 00:21:40

您可以更改字段并使其不为空，而无需检查字段。如果您真的担心不在下班时间执行此操作，您可以向该字段添加一个约束，以检查以确保它不为空。这将允许您使用不检查选项，而不是让它检查 400 万行中的每一行以查看其是否更新。

CREATE TABLE Test
(
    T0 INT Not NULL,
    T1 INT NUll 
)

INSERT INTO Test VALUES(1, NULL) -- Works!

ALTER TABLE Test
    WITH NOCHECK
        ADD CONSTRAINT N_null_test CHECK (T1 IS NOT NULL)

    ALTER COLUMN T1 int NOT NULL 

INSERT INTO Test VALUES(1, NULL) -- Doesn't work now!

实际上，您有两个选项（添加了第三个选项，请参阅编辑）：

使用约束将阻止更新任何新行并保持原始行不变。
将 null 行更新为其他内容，然后应用 not null alter 选项。这确实应该在非工作时间运行，除非您不介意进程被锁定在表之外。

根据您的具体情况，任一选项可能更适合您。我不会选择这个选项，因为你必须在下班时间运行它。从长远来看，与你为了节省几个小时而走捷径可能面临的麻烦相比，你在半夜更新的时间是值得的。

话虽如此，如果您打算选择第二种，您可以最大限度地减少在下班时间所做的工作量。由于您必须确保在更改列之前将行更新为不为空，因此您可以编写一个游标来慢慢地（相对于一次完成所有操作）

遍历每一行，
检查它是否为空，并
适当地更新它。
这将需要一段时间，但它不会锁定整个表并阻止其他程序访问它。（不要忘记 with(rowlock) 表提示！）

编辑：我只是想到了第三种选择：
您可以创建一个包含适当列的新表，然后将原始表中的数据导出到新表。完成此操作后，您可以删除原始表并将新表的名称更改为旧表。为此，您必须禁用对原始依赖项的依赖项，并在完成后将它们重新设置到新依赖项上，但此过程将大大减少您在非工作时间必须完成的工作量。这与您通过 Management Studio 对表进行列排序更改时 sql Server 使用的方法相同。对于这种方法，我会分块进行插入，以确保不会对系统造成撤消压力并阻止其他人访问它。然后在下班时间，您可以删除原来的，重命名第二个，并应用依赖项等。您仍然会有一些下班时间的工作，但与其他方法相比，它会微不足道。

链接到使用 sp_rename。

You can alter a field and make it not null without it checking the fields. If you are really concerned about not doing it off hours you can add a constraint to the field which checks to make sure it isn't null instead. This will allow you to use the with no check option, and not have it check each of the 4 million rows to see if it updates.

CREATE TABLE Test
(
    T0 INT Not NULL,
    T1 INT NUll 
)

INSERT INTO Test VALUES(1, NULL) -- Works!

ALTER TABLE Test
    WITH NOCHECK
        ADD CONSTRAINT N_null_test CHECK (T1 IS NOT NULL)

    ALTER COLUMN T1 int NOT NULL 

INSERT INTO Test VALUES(1, NULL) -- Doesn't work now!

Really you have two options (added a third one see edit):

Use the constraint which will prevent any new rows from being updated and leave the original ones unaltered.
Update the rows which are null to something else and then apply the not null alter option. This really should be run in off hours, unless you don't mind processes being locked out of the table.

Depending on your specific scenario, either option might be better for you. I wouldn't pick the option because you have to run it in off hours though. In the long run, the time you spend updating in the middle of the night will be well spent compared the headaches you'll possibly face by taking a short cut to save a couple of hours.

This all being said, if you are going to go with option two you can minimize the amount of work you do in off hours. Since you have to make sure you update the rows to not null before altering the column, you can write a cursor to slowly (relative to doing it all at once)

Go through each row
Check to see if it is null
Update it appropriately.
This will take a good while, but it won't lock the whole table block other programs from accessing it. (Don't forget the with(rowlock) table hint!)

EDIT: I just thought of a third option:
You can create a new table with the appropriate columns, and then export the data from the original table to the new one. When this is done, you can then drop the original table and change the name of the new one to be the old one. To do this you'll have to disable the dependencies on the original and set them back up on the new one when you are done, but this process will greatly reduce the amount of work you have to do in the off hours. This is the same approach that sql server uses when you make column ordering changes to tables through the management studio. For this approach, I would do the insert in chunks to make sure that you don't cause undo stress on the system and stop others from accessing it. Then on the off hours, you can drop the original, rename the second, and apply dependencies etc. You'll still have some off hours work, but it will be minuscule compared to the other approach.

Link to using sp_rename.

回复收藏 0 原文

鸠魁 2024-08-03 00:21:40

据我所知，“快速”（*）执行此操作的唯一方法是

创建一个“影子”表，该表具有所需的布局
，向源表添加触发器，以便任何插入/更新/删除操作都复制到影子-table（注意捕获可能弹出的任何 NULL！）
将所有数据从源复制到影子表，可能以较小的块形式（确保您可以通过触发器处理已复制的数据，确保数据将适合新结构（ISNULL（？）！）
用脚本输出来自/到其他表的所有依赖项
当所有操作完成后，，在显式事务中执行以下操作：
- 在源表上获取独占表锁，在影子表上获取独占表锁
- 运行脚本以删除对源表的依赖项
- 将源表重命名为其他名称（例如后缀 _old）
- 将影子表重命名为源表的原始名称
- 运行脚本以再次创建所有依赖项

您可能希望在事务之外执行最后一步，因为这可能需要相当多的时间，具体取决于引用此表的表的数量和大小，第一步获胜根本不需要太多时间

与往常一样，最好先在测试服务器上进行测试运行 =)

PS：请不要试图使用 NOCHECK 重新创建 FK，这会使它们变得徒劳，因为优化器不会信任在构建查询计划时也不考虑它们。

（*：快速归结为：尽可能减少停机时间）