• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer
sqlserver tutorial

SQL Server Tutorial

SQL Server Tutorial for Beginners

  • HOME
  • START HERE
  • BASICS
  • ADVANCED
    • SQL Server Index
  • FUNCTIONS
Home / SQL Server Basics / SQL Server ALTER TABLE ALTER Column

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.

Was this tutorial helpful?
YesNo
« Previous Post
Next Post »

Primary Sidebar

DATA MANIPULATION

  • SELECT
  • SELECT TOP
  • SELECT DISTINCT
  • OFFSET FETCH
  • ORDER BY
  • GROUP BY
  • BETWEEN
  • LIKE
  • ALIAS
  • HAVING
  • AND
  • OR
  • IN
  • WHERE
  • SELECT INTO
  • INSERT
  • INSERT Multiple rows
  • INSERT INTO SELECT
  • UPDATE
  • DELETE
  • PRIMARY KEY
  • FOREIGN KEY
  • UNIQUE CONSTRAINT
  • NOT NULL CONSTRAINT
  • SUBQUERY
  • CORRELATED SUBQUERY
  • JOINS
  • CROSS JOIN
  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL JOIN
  • SELF JOIN
  • UPDATE Join
  • CASE
  • COALESCE
  • NULL
  • NULLIF
  • UNION
  • INTERSECT
  • MERGE
  • EXCEPT
  • EXISTS
  • GROUPING SET
  • PIVOT
  • ROLLUP
  • CUBE

DATA DEFINITION

  • CREATE DATABASE
  • DROP DATABASE
  • CREATE SCHEMA
  • ALTER SCHEMA
  • DROP SCHEMA
  • CREATE TABLE
  • RENAME TABLE
  • DROP TABLE
  • TRUNCATE TABLE
  • IDENTITY column
  • Sequence
  • ALTER TABLE ADD Column
  • ALTER TABLE ALTER Column
  • ALTER TABLE DROP Column

Footer

About

SQLServerTutorial.org provides free tutorials and guide on SQL Server for Developers, Database Administrators, and Solution Architects who want to get started SQL Server quickly.

Recent Posts

  • SQL Server ALTER TABLE DROP Column
  • SQL Server ALTER TABLE ALTER Column
  • SQL Server ALTER TABLE ADD Column
  • SQL Server Index on Computed Columns
  • SQL Server Sequence

Quick Links

  • About
  • Contact Us
  • Privacy Policy
  • SQL Server Index
  • Terms of Use

Copyright © 2021 www.sqlservertutorial.org. All Rights Reserved.