How to Alter column size in SQL Server

If you’re using SQL Server, and you want to use T-SQL to change the size of an existing column, this article can help.

By “changing the size of an existing column”, I mean changing the data size. For example, say you have a varchar(255) but you want to increase its size to varchar(500). Here’s what you need to do in that case.

The ALTER TABLE Statement

If you’re doing this with T-SQL, you need to use the ALTER TABLE statement. This statement enables you to change a table’s definition after it has already been created (and it may also contain data).

Here’s an example of changing the size of an existing column:

ALTER TABLE Tasks
    ALTER COLUMN TaskDescription varchar(500);
GO

This example modifies the Tasks table by changing the size of the TaskDescription column.

This obviously assumes that the column (and table) already exist in the database. Otherwise you’ll get an error.

Check the Results

You can check the column size by running a query against INFORMATION_SCHEMA.COLUMNS. Like this:

USE Solutions;
SELECT 
    COLUMN_NAME,
    DATA_TYPE,
    CHARACTER_MAXIMUM_LENGTH,
    CHARACTER_OCTET_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Tasks';

Result:

COLUMN_NAME      DATA_TYPE  CHARACTER_MAXIMUM_LENGTH  CHARACTER_OCTET_LENGTH
---------------  ---------  ------------------------  ----------------------
TaskId           int        null                      null                  
TaskName         nvarchar   255                       510                   
TaskDescription  varchar    500                       500

In this case, we switch to the correct database (Solutions), then we query INFORMATION_SCHEMA.COLUMNS for information about the columns in the Tasks table. We could have narrowed this down to just the column we’re interested in if there were too many columns in the table, but for this example, three columns are no problem.

For Microsoft SQL

For most things, I prefer scripting. But for column changes, I like the UI. Don’t have to remember to check for NOT NULL or any calculations or any additional column properties.

Select table–> Design–> change value in Data Type shown in following Fig.

Save tables design.

Addtional Tips – try uncheck beow option if you got a trouble:

Tools -> Options… / >Designers -> Table and Database Designers — uncheck “Prevent saving changes that require table re-creation”