Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
1  
No (or at least not using documented/supported methods). See Why does ALTER COLUMN to NOT NULL cause massive log file growth? –  Martin Smith Aug 28 '13 at 14:16
1  
It might also possibly be waiting for a Sch-M lock when it takes "forever". Did you look to see if it was waiting or busy? –  Martin Smith Aug 28 '13 at 14:34
    
@MartinSmith I clarified what I mean by forever. I'm testing this in a dev environment with no other sessions hitting the database. It does eventually complete. But the answer that you linked explains why it takes so long. If you can reword your comment as an answer, then I'll accept it. –  Joseph Daigle Aug 29 '13 at 11:55

3 Answers 3

up vote 4 down vote accepted

@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 contain NULL values (and if you know none will be added between checking and adding the constraint) then, as the constraint prevents NULL values being created from future INSERT or UPDATE operations, this will work.

Adding the constraint can still have an impact on concurrent transactions however. The ALTER TABLE will need to acquire a Sch-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 NULLs the constraint is not trusted by the query optimiser which means that the plans can be sub optimal.

CREATE TABLE T (X INT NULL)

INSERT INTO T 
SELECT ROW_NUMBER() OVER (ORDER BY @@SPID)
FROM master..spt_values

ALTER TABLE T WITH NOCHECK
  ADD  CONSTRAINT X_NOT_NULL 
    CHECK (X IS NOT NULL) ; 

SELECT *
FROM T 
WHERE X NOT IN (SELECT X FROM T)

Plan

Compare this with the simpler

ALTER TABLE T ALTER COLUMN X INT NOT NULL

SELECT *
FROM T 
WHERE X NOT IN (SELECT X FROM T)

Plan

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 than WITH 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.

share|improve this answer

You could, instead of altering the column, add a table CHECK constraint with the NOCHECK option:

ALTER TABLE MyTable WITH NOCHECK
  ADD  CONSTRAINT MyColumn_NOT_NULL 
    CHECK (MyColumn IS NOT NULL) ;
share|improve this answer
    
This would prevent future updates or inserts that make the column NULL but would not be able to be used by the query optimiser. –  Martin Smith Aug 28 '13 at 14:22
    
@MartinSmith Oh yes, I just read the answer and comments in the similar question: How do you add a NOT NULL Column to a large table in SQL Server? Please, add an answer with the problems or a better solution and I'll remove mine. –  ypercube Aug 28 '13 at 14:42
    
I don't have a better solution. I upvoted this because it provides a partial solution. If all the OP wants to do is prevent invalid data it will work (and should be quicker than ALTER COLUMN as once the Sch-M lock is acquired this doesn't need to scan the rows at all). Just pointing out that it isn't quite the same (e.g. if used in a NOT IN query the plan will be more complex) –  Martin Smith Aug 28 '13 at 14:51

First of all, what DB platform and version are you using?

Depending on the ALTER TABLE implementation, the server may need to scan each row before completing the operation. You are adding a constraint rather than changing the datatype or size of the column, so I understand your perplexity. Tell us what DB you are using, and someone may be able to find a better answer. My guess, however, is that you will likely be stuck waiting.

share|improve this answer
    
According to the flags, he's running MSSQL 2008. –  Jacobm001 Aug 28 '13 at 17:02

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.