When you create a database table, you specify all columns along with their data types. Once created, there’s not normally any intention of changing these data types. After all, whoever designed the schema would have put a lot of thought into what data type each and every column should accept.

However, we all know that things can change. Despite our best efforts at trying to foresee every possible scenario that may hit our database, sometimes that’s not enough.

So what do we do if we need to use Transact-SQL to change the data type of a column in SQL Server?

We use the ALTER TABLE statement to change it of course.

Example

Here’s an example of using the T-SQL ALTER TABLE statement to change the data type of a column:

ALTER TABLE Tasks
    ALTER COLUMN TaskCode char(6);
GO

This alters the table called Tasks, by changing its TaskCode column to a data type of char(6).  Note that there’s no need to specify what the data type used to be – you simply specify the new data type and be done with it. SQL Server will do the rest.

Check the Results

You can check the result by querying INFORMATION_SCHEMA.COLUMNS:

USE Solutions;
SELECT 
    COLUMN_NAME,
    DATA_TYPE,
    CHARACTER_MAXIMUM_LENGTH AS MAX_LENGTH,
    CHARACTER_OCTET_LENGTH AS OCTET_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Tasks'
AND COLUMN_NAME = 'TaskCode';

Result:

COLUMN_NAME  DATA_TYPE  MAX_LENGTH  OCTET_LENGTH
-----------  ---------  ----------  ------------
TaskCode     char       6           6