SQL Server ALTER field NOT NULL takes forever

Question

I want to alter a field from a table which has about 4 million records. I ensured that all of this 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?

Answer 1

Try adding the No Check Constraint, that way sql server won't check every row in the system to see if it will be in violation. That should speed things up, but I would test it before you try it.

http://doc.ddart.net/mssql/sql70/aa-az_5.htm