Is it possible to modify a datatype of a column when column contains data?

Summary: in this tutorial, you will learn how to use the SQL Server

CREATE TABLE t1 (c INT);

Code language: SQL (Structured Query Language) (sql)6 statement to modify a column of a table.

SQL Server allows you to perform the following changes to an existing column of a table:

• Modify the data type
• Change the size
• Add a

  CREATE TABLE t1 (c INT);

  Code language: SQL (Structured Query Language) (sql)7 constraint

Modify column’s data type

To modify the data type of a column, you use the following statement:

ALTER TABLE table_name ALTER COLUMN column_name new_data_type(size);

Code language: SQL (Structured Query Language) (sql)

The new data type must be compatible with the old one, otherwise, you will get a conversion error in case the column has data and it fails to convert.

See the following example.

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

CREATE TABLE t1 (c INT);

Code language: SQL (Structured Query Language) (sql)8:

CREATE TABLE t1 (c INT);

Code language: SQL (Structured Query Language) (sql)

Second, insert some rows into the table:

INSERT INTO t1 VALUES (1), (2), (3);

Code language: SQL (Structured Query Language) (sql)

Second, modify the data type of the column from

CREATE TABLE t1 (c INT);

Code language: SQL (Structured Query Language) (sql)8 to

INSERT INTO t1 VALUES (1), (2), (3);

Code language: SQL (Structured Query Language) (sql)0:

ALTER TABLE t1 ALTER COLUMN c VARCHAR (2);

Code language: SQL (Structured Query Language) (sql)

Third, insert a new row with a character string data:

INSERT INTO t1 VALUES ('@');

Code language: SQL (Structured Query Language) (sql)

Fourth, modify the data type of the column from

INSERT INTO t1 VALUES (1), (2), (3);

Code language: SQL (Structured Query Language) (sql)0 back to

CREATE TABLE t1 (c INT);

Code language: SQL (Structured Query Language) (sql)8:

ALTER TABLE t1 ALTER COLUMN c INT;

Code language: SQL (Structured Query Language) (sql)

SQL Server issued the following error:

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

Code language: SQL (Structured Query Language) (sql)

Change the size of a column

The following statement creates a new table with one column whose data type is

INSERT INTO t1 VALUES (1), (2), (3);

Code language: SQL (Structured Query Language) (sql)3:

CREATE TABLE t2 (c VARCHAR(10));

Code language: SQL (Structured Query Language) (sql)

Let’s insert some sample data into the t2 table:

INSERT INTO t2 VALUES ('SQL Server'), ('Modify'), ('Column')

Code language: SQL (Structured Query Language) (sql)

You can increase the size of the column as follows:

ALTER TABLE t2 ALTER COLUMN c VARCHAR (50);

Code language: SQL (Structured Query Language) (sql)

However, when you decrease the size of the column, SQL Server checks the existing data to see if it can convert data based on the new size. If the conversion fails, SQL Server terminates the statement and issues an error message.

For example, if you decrease the size of column

INSERT INTO t1 VALUES (1), (2), (3);

Code language: SQL (Structured Query Language) (sql)4 to 5 characters:

CREATE TABLE t1 (c INT);

Code language: SQL (Structured Query Language) (sql)0

SQL Server issued the following error:

CREATE TABLE t1 (c INT);

Code language: SQL (Structured Query Language) (sql)1

Add a 

CREATE TABLE t1 (c INT);

Code language: SQL (Structured Query Language) (sql)7 constraint to a nullable column

The following statement creates a new table with a nullable column:

CREATE TABLE t1 (c INT);

Code language: SQL (Structured Query Language) (sql)2

The following statement inserts some rows into the table:

CREATE TABLE t1 (c INT);

Code language: SQL (Structured Query Language) (sql)3

If you want to add the

CREATE TABLE t1 (c INT);

Code language: SQL (Structured Query Language) (sql)7 constraint to the column

INSERT INTO t1 VALUES (1), (2), (3);

Code language: SQL (Structured Query Language) (sql)4, you must update NULL to non-null first for example:

CREATE TABLE t1 (c INT);

Code language: SQL (Structured Query Language) (sql)4

And then add the

CREATE TABLE t1 (c INT);

Code language: SQL (Structured Query Language) (sql)7 constraint:

CREATE TABLE t1 (c INT);

Code language: SQL (Structured Query Language) (sql)5

In this tutorial, you have learned how to use the SQL Server

CREATE TABLE t1 (c INT);

Code language: SQL (Structured Query Language) (sql)6 to modify some properties of an existing column.

How do you change the datatype of a column with data?

How to change column datatype in SQL database without losing data?

How to change column datatype in Oracle without losing data?

Which syntax will modify the data type of an already existing column?