ALTER COLUMN in SQL Server

Introduction

The modified column cannot be any one of the following:

Sample data population

create table Employees( Employee_id int primary key)
insert Employees values(100)
GO
create table TestAlterColTable
(
       id int identity(1, 1) not null
              primary key clustered,
       SomeLongText nvarchar(512) not null
              default replicate('a', 512),
                        timeStampCol timestamp,
            computedColumn as id*10,
            FKCol int references employees(employee_ID) default 100
);
go
--Sampling data
insert into TestAlterColTable(SomeLongText) values(default);
go 10

select *,
       len(SomeLongText) as SomeLongText_length,
       timeStampCol,
       computedColumn,
       FKCol
from TestAlterColTable;

• This is not possible due to timestamp type

alter table TestAlterColTable
alter column
       timeStampCol timestamp NOT NULL; 
GO

• Not possible due to computed column or part of computed column

alter table TestAlterColTable
alter column computedColumn int NOT NULL; 
GO

•  Altering datatype is not possible due to 'Used in a PRIMARY KEY or [FOREIGN KEY] REFERENCES constraint'.

--Alter COLUMN is possible in the case of column used in a CHECK or UNIQUE constraint. However, changing the length of a variable-length column used in a CHECK or UNIQUE constraint is allowed.

alter table TestAlterColTable
alter column FKCol BIGINT NULL;
GO

• A columns included in a primary key constraint, cannot be changed from NOT NULL to NULL.

-- not possible due to its a PK column

alter table TestAlterColTable
alter column ID INT NOT NULL;
GO

• Associated with a default definition. However, the length, precision, or scale of a column can be changed if the data type is not changed.

--The data type of a column of a partitioned table cannot be changed.

-- this is not possible due to truncate

alter table TestAlterColTable
alter column SomeLongText nvarchar(256);
go

• here length is larger than original. So its possible

alter table TestAlterColTable
alter column
       SomeLongText nvarchar(1024);
go

• Also possible (no truncation) because the length is same to original and we are making this column as NULLable

alter table TestAlterColTable
alter column
       SomeLongText nvarchar(512) null;
go
insert into TestAlterColTable
values(null); 

• The data type of columns included in an index cannot be changed unless the column is a varchar, nvarchar, or varbinary data type, and the new size is equal to or larger than the old size.

create unique index IX_TestAlterColTable_SomeLongText
on TestAlterColTable(id) include(SomeLongText);
go

• Not possible, as an index depends on the column

alter table TestAlterColTable
alter column
       SomeLongText varchar(512) not null;
go

Clean Up code

drop table TestAlterColTable
go
drop table employees
go

Conclusion

Two more scenarios where ALTER COLUMN is not possible are:

1. 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.
2. The ROWGUIDCOL for the table