ALTER COLUMN in SQL Server

Bandi
Posted by in Sql Server category on for Beginner level | Points: 250 | Views : 1618 red flag

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:

  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


Page copy protected against web site content infringement by Copyscape

About the Author

Bandi
Full Name: Chandrika Bandi
Member Level: Platinum
Member Status: Member,MVP
Member Since: 7/23/2013 5:52:37 AM
Country: India
[B]Mark This Response as Answer[/B] -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif
http://www.dotnetfunda.com

Login to vote for this post.

Comments or Responses

Posted by: Joginder on: 1/6/2015 | Points: 25
article design not good, plz rearrange your article again.

Login to post response

Comment using Facebook(Author doesn't get notification)