ALTER COLUMN的执行时间 [英] Execution time of ALTER COLUMN

问题描述

具有60列200行的表。现在将 BIT 列从 NULL 更改为 NOT NULL 运行时间超过3小时。为什么花这么长时间？

Having a table with 60 columns, and 200 rows. Altering a BIT column from NULL to NOT NULL, now has a running execution time of over 3 hours. Why is this taking so long?

这是我正在执行的查询：

This is the query that I'm execution:

ALTER TABLE tbl
ALTER COLUMN col BIT NOT NULL

是否存在除了创建新列，用旧列中的值更新它，然后删除旧列并重命名新列之外，这是一种更快的方法？

Is there a faster way to do it, besides creating a new column, updating it with values from the old column, then dropping the old column and renaming the new one?

这是在MS SQL Server 2005上。

This is on MS SQL Server 2005.

推荐答案

ALTER是否被其他语句持有的元数据共享锁所阻止？任何ALTER都需要元数据互斥锁，因此将被使用该表的任何其他语句阻塞。

IS the ALTER blocked by metadata shared locks held by other statements? Any ALTER requires a metadata exclusive lock, and as such will be block by any other statement that uses the table.

检查活动监视器，或查看 sys.dm_exec_requests ，看看谁在阻止您的ALTER INDEX。

Check Activity Monitor, or look into sys.dm_exec_requests and see who's blocking your ALTER INDEX.

这篇关于ALTER COLUMN的执行时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！