SQL Server ALTER TABLE ALTER Column

Learning Objective

The objective of this SQL Server tutorial is to teach you how to modify one or more columns of a table using SQL Server ALTER TABLE ALTER Column statement.

Introduction to SQL Server ALTER TABLE ALTER Column

Sometimes you may need to modify an existing column of a table. SQL Server ALTER TABLE ALTER Column allows you to modify any existing column of a table without dropping
and recreating a table.

SQL Server allows you to perform below modification of a coulmn.

  1. Modification of DATA TYPE
  2. Changing the size of the column
  3. Adding NOT NULL column

SQL Server ALTER TABLE ALTER Column Syntax and Examples

1) Modification of DATA TYPE

The follow is the syntax of modifying data type of a column.

ALTER TABLE table_name
ALTER COLUMN column_name new_data_type(size);

Remember that, new data type should be compatible with the old one. Otherwise, SQL Server gives a conversion error, if the data is present in the existing column and fails to convert.

Let check an example for the same.

First, create a table with one column whose data type is INT.

CREATE TABLE test1 (a INT);

Second, insert few rows into the table.

INSERT INTO test1
VALUES
(5),
(10),
(15);

Third, modify the column of the table to varchar(2).

ALTER TABLE test1 ALTER COLUMN a VARCHAR(2);

Fourth, check the definition of the table now as below:

SP_HELP test1;
SQL Server alter table alter column

Fifth, insert another row to the table.

INSERT INTO test
VALUES
('$');

Sixth, modify the data type of the column back to INT again.

ALTER TABLE test1 ALTER COLUMN a INT;

SQL Server gives the following error:

Conversion failed when converting the varchar value '$' to data type int.

2) Changing the size of the column

SQL Server allows you to change the size of a column.

First, create a new table name test2 with a column named b.

CREATE TABLE test2 (b varchar(10));

Second, insert some records into the table.

INSERT INTO test2
VALUES
('SQL Server'),
('MySQL'),
('Oracle');

Third, incease the size of the table as below:

ALTER TABLE test2 ALTER Column b VARCHAR(20);

However, if you try to decrease the size of the column, SQL Server checks if the data present in the column can accommodate in the new size. If the size is not enough, then the conversion will fail and SQL Server gives an error.

ALTER TABLE test2 ALTER Column b VARCHAR(5);

SQL Server will give following error.

String or binary data would be truncated.

3) Adding NOT NULL constraint to nullable column

The following statement creates a new table name test3 with nullable column.

CREATE TABLE test3 (c VARCHAR(50));

The below statement insert few records into the table.

INSERT INTO test3
VALUES
('Apple'),
('Mango'),
(NULL);

If you want convert a nullable column to NOT NULL column, first you need convert all the NULL values to NOT-NULL.

UPDATE test3
SET c=''
WHERE c IS NULL;

Now convert the column by adding NOT NULL constraint.

ALTER TABLE test3 ALTER COLUMN c VARCHAR (20) NOT NULL;

Summary

In this tutorial, you have learned how to use SQL Server ALTER TABLE ALTER Column to modify one or more existing columns of a table.

Advertisement