How to change the data type of a column in a table

Aman Raiyyani
Change data type of a column in a table

To modify the data type of a column:

Please note: Whenever making a change to a table structure there is a chance that change may result in data corruption if not done correctly, which is why it is would advisable to take a backup/copy of the table on which you intend to make the change. You can do this simply by running the following statement:

use nameofthedatabase
select * into dbo.name_of_the_table_backup from dbo.name_of_the_table

Using T-SQL

To change the data type of a column

In Object Explorer, connect to the desired server the table resides in

On the Standard bar, select New Query. Select the database in which the table resides by choosing from the drop-down on the top-left corner or

use name_of_database;

Copy and paste the following example into the query window and select Execute.


ALTER TABLE name_of_table
ALTER COLUMN name_of_column datatype;

Using Object Explore/Graphical Interface:

In Object Explorer, right-click the table with columns you wish to amend the data type and select Design.

Select the column for which you want to amend the data type.

In the Column Properties tab, select the grid cell for the Data Type property and choose a new data type from the drop-down list.

On the File menu, select Save table name.


When you change the data type of a column, Table Designer applies the default length of the data type you selected, even if you have already specified another. Always set the data type length for to the desired value after specifying the data type.

Be thoughtful about the data type you are changing, if the data type is not applicable to data in the column, there are chances of data loss or data integrity.


If you attempt to change the data type of a column that relates to other tables, Table Designer asks you to confirm that the amendment should be made to the columns in the other tables.

Explore our range of trailblazer services

Risk and Health Audit

Get 360 degree view in to the health of your production Databases with actionable intelligence and readiness for government compliance including HIPAA, SOX, GDPR, PCI, ETC. with 100% money-back guarantee.

DBA Services

The MOST ADVANCED database management service that help manage, maintain & support your production database 24×7 with highest ROI so you can focus on more important things for your business

Cloud Migration

With more than 20 Petabytes of data migration experience to both AWS and Azure cloud, we help migrate your databases to various databases in the cloud including RDS, Aurora, Snowflake, Azure SQL, Etc.

Data Integration

Whether you have unstructured, semi-structured or structured data, we help build pipelines that extract, transform, clean, validate and load it into data warehouse or data lakes or in any databases.

Data Analytics

We help transform your organizations data into powerful,  stunning, light-weight  and meaningful reports using PowerBI or Tableau to help you with making fast and accurate business decisions.

Govt Compliance

Does your business use PII information? We provide detailed and the most advanced risk assessment for your business data related to HIPAA, SOX, PCI, GDPR and several other Govt. compliance regulations.

You May Also Like…