What is Data Type?

What is meant by data type in database or programming languages? Datatype means what kind of data you want to store in that variable or column. For example, if you want to store the name of customer you most select string or varchar data type of that column, not number datatype.

► Play

How to Change the Datatype of the column?

SQL provides multiple ways to change your table columns datatype and want to add constraints in that column. We can easily change columns datatype through a simple query. Almost all the Database management systems provide queries as well as graphical user interfaces to change the datatypes of the columns.

Change the column datatype Using Query in SQL

To change the data type of a column, you can use the following query:

ALTER TABLE table_name

ALTER COLUMN column_name new_data_type ( size ) ;

The new datatype must be compatible with the old datatype of that column, otherwise, SQL gives you an error for modification of datatype.

Also, database management systems provide build-in functions to change the existed columns datatypes. Like this,

ALTER TABLE ‘ table_name ‘
MODIFY ‘ column_name ‘ ‘ New Data Type ‘  ;

Example

ALTER TABLE Customer ALTER COLUMN Address char ( 100 ) ;

OR

ALTER TABLE Customer MODIFY Address char ( 100 ) ;

How to Change the Datatype of the column in SQL workbench?

You can also change the data type of a column in SQL using the graphical user interface, you can change through these steps:

Click on the table and right-click on the schema of that table and then select the ALTER TABLE. A window will appear like this:

  • Select the column name available on the screen, edit this and click on apply.

You changed the datatype of the column.

How to change datatype in SQL after data is filled

It’s possible to change the datatype using Alter Column command, only if the new datatype is compatible for the old datatype otherwise SQL not give you permission to change the datatype.

Example

You may change a column from varchar ( 50 ) to a varchar ( 200 ) not a number or date data type, the query is given below.

ALTER TABLE TableName

ALTER COLUMN ColumnName nvarchar ( 200 ) ;

How to Change the Column without Dropping a Table.

  • Create a new column and copy the data into this column.
  • Delete or drop the old column.
  • Change the name of the new column to old column

Example:

– – Add the new column

ALTER TABLE TableName

ADD DescriptionNew VARCHAR ( MAX )

GO

– – Copy data to new column

Update TableName

SET DescriptionNew = Description

GO

— Drop old column

ALTER TABLE TableName

DROP COLUMN Description

GO

— Rename the new column to the original column’s name.

sp_RENAME ‘ TableName.DescriptionNew ‘ , ‘ Description ‘ , ‘ COLUMN ‘

GO

This the simple and easy way to change the data type of any column and name of the column without dropping it.

Send us a feedback0/500

Do you like this article?
Yes
No