ALTER TABLE command with no default value taking forever?

I'm attempting to add a NOT NULL constraint to a table I have (so I can use it as part of a primary key, so I can create a spatial index...) but it is taking a really long time - 1 hour 31 minutes and counting.

I had read this post where the answer seemed to be that it was taking so long because the person was adding a default value but i'm not doing that:

ALTER TABLE unacastVisitRaw ALTER COLUMN timestamp varchar(64) NOT NULL

The table is rather large (290 million records) but it still seems like a long time for just adding a constraint and not changing any values.

Is there anything I can do to speed up this operation? Why would it be taking so long if I'm not updating the existing records?

UPDATE: Here are the wait stats for the session

Why would it be taking so long?

You can look at the Session Wait Stats to see. eg

declare @session_id int = 121 

select top 10 *
from sys.dm_exec_session_wait_stats
where session_id = @session_id
order by wait_time_ms desc

select command, wait_type, wait_time, last_wait_type, cpu_time, writes, total_elapsed_time
from sys.dm_exec_requests 
where session_id = @session_id

where 121 is the session id of the session running the query.


Why would it be taking so long if I'm not updating the existing records?

It probably wouldn't. Changing the nullability is a change to the data type, not a constraint. And it appears that it requires a rewrite of your table.

StackExchange Database Administrators Q#222386, answer score: 2