Altering nullability of column in SQL Server

Question

Say I have the following table:

CREATE TABLE test(test_id int not null identity primary key,
 field1 int not null,
 field2 int
);
CREATE INDEX IDX_test_field1 ON test(field1); 
CREATE INDEX IDX_test_field2 ON test(field2); 

Now ALTER TABLE test ALTER COLUMN field1 int works, and field1 allows null.

Nevertheless, I cannot change ALTER TABLE test ALTER COLUMN field2 int not null because of

ALTER TABLE ALTER COLUMN field2 failed because one or more objects access this column.

Also, I cannot change field1 back to not null.

However, I can add and then drop check constraint as many times as I want :

ALTER TABLE test  ADD CONSTRAINT CHK_NN_field2 CHECK (field2 IS NOT NULL);   
DROP CONSTRAINT CHK_NN_field2` without any problems.

Is it well defined behaviour? Could someone explain why that happens or point me to documentation?

I'm using SQL Server 2008 R2 if that matters.

Thank you.

Answer 1

The relevant documentation is here

ALTER COLUMN Specifies that the named column is to be changed or altered.

The modified column cannot be any one of the following:

• Used in an index, unless the column is a varchar, nvarchar, or varbinary data type, the data type is not changed, the new size is equal to or larger than the old size, and the index is not the result of a PRIMARY KEY constraint.

• Used in statistics generated by the CREATE STATISTICS statement unless the column is a varchar, nvarchar, or varbinary data type, the data type is not changed, and the new size is equal to or greater than the old size, or if the column is changed from not null to null. First, remove the statistics using the DROP STATISTICS statement. Statistics that are automatically generated by the query optimizer are automatically dropped by ALTER COLUMN.

In practice it seems that SQL Server does allow some additional cases beyond that mentioned in the documentation however.

As you show in your question ALTER TABLE test ALTER COLUMN field1 int null does in fact work so the restrictions on changes to columns used in indexes appear to be the same as those for user created statistics.

Moreover the caveat mentioned about Primary Keys seems to be untrue also. The following works fine.

CREATE TABLE test2(pk varchar(10) primary key);

ALTER TABLE test2 ALTER COLUMN pk varchar(100) NOT NULL

Answer 2

Your indexes on field1 and field2 are preventing you from changing the columns from NULL to NOT NULL. Drop the index, change it to NOT NULL and then recreate the index.

Answer 3

NULL is a looser restriction than NOT NULL.

What would happen if you had NULL fields when you try to change to NOT NULL (constraint violation and fail), vs. when you have NOT NULL, changing to NULL (no impact).

When you create a constraint, the data must be validated to conform to that constraint prior to the table alteration (or it fails) - from what I've observed this behavior doesn't occur on alter or drop & instead changing constraints in this way is simply prevented.