Change Datatype of Column in SQL

Overview

SQL is quite a dynamic and manipulative query language. It allows us to change and present data in the database in various types depending upon our views and requirements. The data present in the database is both unorganised and organised.

Each group of objects or data may have different data types, which modify depending on the current requirements of the database.

How to Change Datatype of Column in SQL?

In SQL, we can change the data types of the data present in rows and columns of the database in different ways. Let's explore ways to change the data type of columns in the SQL database.

Method 1 - Using SQL Server

To change the data type of a column in SQL, we have to open the SQL server.

Select the Object Explorer option.
Right-click on the column that needs to be changed.
Click on Design.
Click on the grid cell in the column properties to change their data type.
Choose the new data type of the columns from the list of data types in the drop-down list.
To save the changes, click the save table option in the files menu.

change-datatype-of-column-sql

While changing the data type of a column in the SQL server, you can also change the length of the selected data type. It gets done using the Table designer option. The size of the desired data type also needs to be specified.

Method 2 - Using ALTER TABLE Command

The ALTER TABLE command helps add, delete, and modify columns in a table already present in the SQL database. It also alters the table by adding or dropping constraints on the existing table.

Syntax

Examples to Change the Datatype of Column in SQL

Let us see some examples.

The above SQL statement modifies the table Students by adding a column name with the varchar datatype of size 100.

The above SQL statement modifies the table Employees by adding a column employee_name with the string datatype.

The ALTER TABLE statement not just deletes or adds columns in the database. The ALTER TABLE statement also allows us to change the datatype of a column in SQL in an existing table using the ALTER COLUMN option.

The above SQL statement modifies the column datatype of the column DateofBirth to the type year. SQL displays the year in the format YYYY with values ranging from 1901 to 2155.

We have seen various examples of the statement having simple syntax to modify or change the datatype of a single column in SQL. The SQL statement becomes complex when it changes the datatype of multiple columns in the database. The name of the required type is written along with the column names to make the datatype conversion.

Conclusion

The datatype of a column or multiple columns can be changed using different methods.
The first method is to make the necessary changes using the SQL server.
The second method is to use the ALTER table statement in SQL to add or modify the datatype of the columns. We can change or add multiple columns with the required type.