T-SQL Tutorial

Alter Table Alter Column


In SQL Server, the ALTER TABLE statement is used to modify the structure of an existing table. The ALTER COLUMN command is specifically used to modify an existing column within a table. This command allows you to change the data type, length, nullability, and other attributes of a column.


Syntax

Here is the syntax for using the ALTER TABLE ALTER COLUMN command:

ALTER TABLE table_name
ALTER COLUMN column_name new_data_type [column_attributes]

Let's break down the syntax:

ALTER TABLE is the command used to specify that you want to modify a table.
table_name is the name of the table that contains the column you want to modify.
ALTER COLUMN is a clause that indicates you want to alter the specified column.
column_name is the name of the column you want to modify.
new_data_type is the new data type you want to assign to the column.
[column_attributes] represents optional column attributes such as NULL/NOT NULL, DEFAULT values, etc.


Example

Here's an example that demonstrates how to use the ALTER TABLE ALTER COLUMN command:

Suppose we have a table called "Employees" with a column named "Salary" that currently has a data type of INT, and we want to change it to DECIMAL(10,2) and make it a non-nullable column:

ALTER TABLE Employees
ALTER COLUMN Salary DECIMAL(10,2) NOT NULL

In this example, we used the ALTER TABLE statement to specify the table "Employees." Then we used the ALTER COLUMN command to modify the "Salary" column, changing its data type to DECIMAL(10,2) and making it a non-nullable column (NOT NULL).

Remember, when modifying a column, you should be cautious about any potential data loss or impacts on existing data. It's recommended to take a backup of the table before making any structural changes.

Additionally, note that the ALTER TABLE ALTER COLUMN command might have limitations depending on the version of SQL Server you are using, so it's always a good practice to consult the official documentation or consult the database administrator for specific details related to your SQL Server version.