Why does adding a nullable default constraint to an existing column take so long?

One possibility may be that you have another process on your server that's locking this table.

Imagine I have two SSMS windows open, and in the first one I execute these commands:

-- Session 1CREATE TABLE Foo(IsTrue BIT) INSERT INTO Foo VALUES (1),(1),(0)BEGIN TRANSACTIONUPDATE Foo SET IsTrue = 1 - IsTrue

And then leave the SSMS window open so that the transaction never closes, trying to execute this simple constraint command in the other SSMS session will hang forever:

-- Session 2ALTER TABLE Foo ADD CONSTRAINT FooDefault DEFAULT(0) FOR IsTrue

Note that in this example, the size or complexity of the table is irrelevant; I'm forced to wait for the transaction to complete. My alter instruction in session 2 won't complete until I release the lock on Foo either by COMMITing the transaction or closing session 1.

How can you tell if this is your problem? Have a look at the "Processes" list in the SSMS activity monitor. If your ALTER instruction is waiting for something else to complete, there'll be a number in the "Blocked By" column indicating the Session ID of the command that's causing your problem.

That session may in turn be waiting on another and so forth. If you follow these references, you eventually find a process with a 1 in the "Head Blocker" column. From there you can decide whether the appropriate action is to kill the offending process, or just wait it out.

1. recreate the object with all the constrains
2. dump the data
3. lock the original object
4. switch the object names

this way is the fastest if you want to optimize, re-index and avoid conflicts like the one mentioned by Dan