Change datatype of column in SQL

SQL being a dynamically manipulating database query language lets you play with your data-set that may be organized or unorganized. Such data may be presented in the form of different types depending upon your requirements. There are various methods to change the types of data present in the rows or columns of your database. Here, we will discuss the method to change the datatype of column in SQL.

Using SQL server

1. Open the SQL server. In the Object Explorer option, right-click the column you want to change and click on Design.
2. You need to select the column whose data type you want to modify.
3. In the Column Properties, you need to click the grid cell to change the Data Type property and then choose the data type from the appeared drop-down list.
4. Now, click Savetable on the File menu to save the changes.

Note: Whenever you modify the column data type in the SQL server, the option Table Designer applies the changes related to the length of the selected data type. You may always need to specify the length of the data type along with desired specified value after the data type.1.

Using ALTER TABLE

The ALTER TABLE command in SQL lets you delete, add or modify columns present in your database table. It is also used for other purposes like adding or dropping constraints on your existing database table. Create the sample database shown in the below examples. Proceed with the below steps to understand how the data type is changed.

Syntax:

ALTER TABLE table_name  

ADD column_name datatype; 

Example:

ALTER TABLE Students  

ADD name varchar(100); 

To modify the datatype of the column:

Syntax:

ALTER TABLE table_name  

ADD column_name datatype;

Example:

ALTER TABLE Employees  

ADD employee_name string;

Also, using the ALTER COLUMN option in SQL, you can easily modify the data type of the given column as shown. The below query changes the datatype of the column named DateofBirth to the type year.

ALTER TABLE Employees  

ALTER COLUMN DateofBirth year;

The main purpose of the alter command is not just to delete or add the columns present in your database but to modify and change it too. In the above examples, you have seen the simple and easy syntax of ALTER TABLE command in SQL. There might also arise a situation when you want to modify multiple columns in the database. To do that, you simply need to assign the column’s name along with the datatype conversion you want in your newly modified column. Consider the below example.

ALTER TABLE table_name  

  ADD (column_1 column_definition,  

       column_2 column_definition,  

       ...  

       column_n column_definition);

Using other databases

For Oracle, MySQL, MariaDB:

ALTER TABLE table_name  

  MODIFY column_name column_type;

For POSTgreSQL:

ALTER TABLE table_name  

 ALTER COLUMN column_name TYPE column_definition; 

Also, if you do not want to lose data while changing the datatype of the respective column, you might see the below example for reference.

Create table Employees  

(  

ID int primary key ID,  

Name varchar(50),  

Sex varchar(50),  

Incentives nvarchar(50)  

)  

To know what datatype your column is, you need to type the below command which tells you the data type of the column you want to change.

Syntax

SELECT datatype from Table.COLUMS  

WHERE Table.schema = "Your_database_name"  

AND table_name = "Your_table_name"  

To understand this is quite a depth, let’s create a database to observe how datatypes of columns can be brought out.

In MySQL  

  

create table DataTypeDemo  

(  

   Id int,  

   Venue varchar(100),  

   Amount  decimal(9,3)  

);

Query:

SELECT datatype from Table.COLUMNS  

WHERE table_schema = "Company"  

AND table_name = "Attendance" 

In the above example, the output of the query will roll out the datatype of the respective columns. We used MySQL since the syntax is quite familiar and easy to understand.

Summary

In this article, you learned how you can easily change the data types of your desired columns in SQL, MySQL, or any other databases you might be using. There are no such hard and fast rules to write the queries in capital or small letter provided some data types are case-sensitive and should be used only with prior knowledge. If you’re working with huge amounts of data, rolling out all the data types back to previous data types is not an easy task; rather you would find it more difficult to arrange them after converting. Thus, one should carefully figure out the fragile measures before opting to change the data types of the columns in your desired database table.