The ALTER COLUMN T-SQL portion of the ALTER TABLE command is used to change a named column. There is a list of scenarios where this is not possible, such as a column used in a PRIMARY KEY or FOREIGN KEY constraint.
Introduction
We are going to look into each of the scenario where the column ALTER is not applicable and where it is applicable.
Below shows some of these examples of what is possible
and not possible: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:
- 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.
- The ROWGUIDCOL for the table