I have a table with millions of rows and a column that allows NULL values. However no row currently has a NULL value for that column (I can verify this fairly quickly with a query). However when I execute the command
ALTER TABLE MyTable ALTER COLUMN MyColumn BIGINT NOT NULL;
the query takes forever relatively speaking. It actually takes between 10 and 20 minutes, more than twice as long as adding a check constraint. Is there a way to instantly update the table's metadata for that column, especially since I know that no row has a NULL value for that column?
Best Answer
@ypercube's answer does manage this partially as a metadata only change.
Adding the constraint with
NOCHECK
means that no rows will need to be read to verify it, and if you are starting from a position where the column does not containNULL
values (and if you know none will be added between checking and adding the constraint) then, as the constraint preventsNULL
values being created from futureINSERT
orUPDATE
operations, this will work.Adding the constraint can still have an impact on concurrent transactions however. The
ALTER TABLE
will need to acquire aSch-M
lock first. Whilst it is waiting for this all other table accesses will be blocked as described here.Once the
Sch-M
lock is acquired the operation should be pretty quick however.One problem with this is that even if you know the column in fact has no
NULL
s the constraint is not trusted by the query optimiser which means that the plans can be sub optimal.Compare this with the simpler
One possible problem you might encounter with altering the column definition in this way is that it not only needs to read all the rows to verify that they meet the condition but also can end up actually performing logged updates to the rows.
A possible half way house might be to add the check constraint
WITH CHECK
. This will be slower thanWITH NOCHECK
as it needs to read all rows but it does allow the query optimiser to give the simpler plan in the query above and it should avoid the possible logged updates issue.