Change Datatype of Column in SQL
FREE
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.
Transform Your Career
Choose from our industry-leading programs designed for career success
Modern Software and AI Engineering Program
Master full-stack development with AI integration
+1000 moreModern Data Science and ML with specialisation in AI
Advanced data science techniques with AI specialization
+1000 moreAdvanced AIML with Specialisation in Agentic AI
Deep dive into AIML with focus on Agentic systems
+1000 moreDevOps, Cloud & AI Platform Engineering
Build and manage AI-powered cloud infrastructure
+1000 moreAI Engineering Advanced Certification by IIT-Roorkee
Premier AI engineering certification from IIT-Roorkee
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.
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.
Scaler Placement Report and Statistics
Scaler learners achieved 2.5x salary growth with average post-Scaler CTC reaching ₹23L.
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.
Google
Amazon
Microsoft
Flipkart
Adobe1200+ more