SQL Server ALTER TABLE

In SQL Server, there are various commands to Add, Update, and Delete a Database Schema called DDL or Data Definition Language commands. A Table in a database is part of the database schema and the ‘ALTER TABLE Moify Column‘ command is used to make changes to the database table column data type, column size, and if the column can accept NULL or NOT NULL value. In this article, we will look at the ALTER TABLE, and DDL command to make changes to an existing Table Column in detail.

Uses of ALTER TABLE Modify Column

There are 3 different changes we can make to a Column in the SQL Table with the ALTER COLUMN option in the ALTER TABLE command.

• We can change the Table Column Size.
• We can change the Table Column Datatype.
• We can change the Table Column constraints from NULL to NOT NULL.

Command Syntax:

Below is the Syntax for modifying a table column in SQL Server.

ALTER TABLE <table_name>
ALTER COLUMN <column_name> <column_type> (size)

Below is the Table Schema of the Students table used in the examples below:

Students Table

ALTER COLUMN command is explained below with examples.

Change Table Column Size

To explain this command with an example, in the ‘Students‘ Table to modify the size of the ‘Course‘ column from 60 to 80, the below command will change the size of the varchar column as per the size specified.

ALTER TABLE Students 
ALTER COLUMN Course varchar (80)

Explanation: If the command executes correctly, then this message ”Commands completed successfully” will be displayed in the output window in MS SQL Server.

In above Query, ‘Students‘, on which the change need to be done is given after ALTER TABLE key words. After the key word ALTER COLUMN the column name, ‘Course‘ from the table ‘Students‘ is provided to make the required changes in the specified column. After the column name the specified changes are given, and in this example 1, the column size change is specified to 80. The existing column size of ‘Course‘ is 60. When the above query is executed successfully the column, ‘Course‘ will be changed to 80.

Change Column Type

In the below example, We change the column type of Student_Name in the ‘Students‘ Table from nvarchar type to varchar type.

ALTER TABLE Students 
ALTER COLUMN Student_Name varchar (60) 

Explanation: If the command executes correctly then this message Commands completed successfully will be displayed in the output window in MS SQL Server.

The above query is designed to change the column ‘Student_Name‘ type in table ‘Students‘ from existing ‘nvarchar‘ to ‘varchar‘. Once the above query is executed successfully, the column type of ‘Student_Name‘ is changed to ‘varchar‘

Change NULL value Constraint to NOT NULL Constraint

In the below example, in the ‘Students‘ Table, for the ‘Student_Name ‘ column the column constraint is changed from NULL to NOT NULL. The NULL value is the default value added when we create a column in a table under ‘Allow Nulls‘ column of table schema.

ALTER TABLE Students 
ALTER COLUMN Student_Name varchar (60) NOT NULL

Output: If the command executes correctly the below message will be displayed in the output window in MS SQL Server.

Explanation: When the above query is executed successfully, the ‘Students‘ table column ‘Student_Name‘ has ‘Allow Null’ option from existing, ‘NULL‘ to ‘NOT NULL‘ as it is specified in the query after the column size.

Error while Modifying Column

When there is data already exists in a existing table column, then changing the column type from one type to another may fail due to data mismatch.

For example, if the exiting data is text and if the new data type is numeric then it will generate error in result.

Query

ALTER TABLE Students 
ALTER COLUMN Course Int 

Explanation: The above query tries to change, the ‘Course‘ column in the ‘Students‘ table from data type of ‘varchar‘ to data type of ‘int‘. This query will result in error when executed since the column already has text data, and it can be converted to data type of ‘int’.

If the size is reduced from existing value to a lower value, then if there is a bigger size data in the column than the given new column size, this will result in error. But if the existing data is smaller in size then the changed size, then the size will be changed.

ALTER TABLE Students 
ALTER COLUMN Student_Name varchar(10)

The above query tries to change, the ‘Student_Name‘ column in the ‘Students‘ table from column size of ‘60‘ to column size of ‘10‘. This query will result in error when executed since the column already has text data of size greater than 10.

Output:

When this command is executed the below error will be displayed:

String or binary data would be truncated. The statement has been terminated.

Conclusion:

ALTER TABLE modify column command is a very useful command to make changes to an existing table schema in situations where we need to make changes from the existing table schema. Excersise caution when making changes with a table already having data.