Altering nullability of column in SQL Server

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.

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

StackExchange Database Administrators Q#27262, answer score: 4