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 TableALTER 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.
Similar Reads
CREATE TABLE in SQL Server
SQL Server provides a variety of data management tools such as querying, indexing, and transaction processing. It supports multiple programming languages and platforms, making it a versatile RDBMS for various applications. With its robust features and reliability, SQL Server is a popular choice for
4 min read
PostgreSQL - ALTER TABLE
In PostgreSQL, the ALTER TABLE statement is a powerful and essential tool that allows us to modify the structure of an existing table to meet evolving database needs. With PostgreSQL ALTER TABLE, we can perform various modifications on the table without disrupting the ongoing operations of our datab
6 min read
SQL ALTER TABLE
The SQL ALTER TABLE statement is a powerful tool that allows you to modify the structure of an existing table in a database. Whether you're adding new columns, modifying existing ones, deleting columns, or renaming them, the ALTER TABLE statement enables you to make changes without losing the data s
5 min read
SQL Server Describe Table
SQL Server is a widely used Relational Database Management System (RDBMS) that allows users to create and manage databases effectively. When working with databases in SQL Server it is essential to understand the schema of the tables present in the database. Describing a table means getting informati
4 min read
SQL Server ALTER TABLE DROP COLUMN
In SQL Server, there could be some situations when we may have to delete or drop a column from a table. Sometimes the column in a table was created wrongly or maybe it is no longer required and has become obsolete. So, to drop a column from a table, the ALTER TABLE, DROP COLUMN SQL query is used. In
4 min read
SQL Server Copy Table
Copying or replicating tables is one of the crucial functions of database management systems. Copy table is a crucial option to create table data backups or to create duplicate data from a table to another table with few columns or some of the data for various purposes. In this article, We will lear
5 min read
SQLite Alter Table
SQLite is a serverless architecture that we use to develop embedded software for devices like televisions, cameras, and so on. It is written in C programming Language. It allows the programs to run without any configuration. In this article, we will learn everything about the ALTER TABLE command pre
4 min read
Magic Tables in SQL Server
Magic tables are the temporary logical tables that are created by the SQL server whenever there are insertion or deletion or update( D.M.L) operations. The recently performed operation on the rows gets stored in magic tables automatically. These are not physical table but they are just temporary int
3 min read
Reverse PIVOT Table in SQL Server
In SQL Server, the PIVOT operation is a powerful feature that allows you to transform rows into columns, providing a convenient way to structure and analyze data. However, there are situations where we may need to reverse this operation, converting columns back into rows. This process is commonly kn
7 min read
SQL Server DELETE and DROP TABLE Commands
In SQL Server, managing data involves not only inserting and updating records but also removing them when they're no longer needed. Two important commands for data removal are DELETE and DROP TABLE. These commands play crucial roles in maintaining database integrity and managing database structures.
4 min read