Sunday, March 27, 2016

How to increase length of existing VARCHAR column in SQL Server

You can increase the length of a VARCHAR column without losing existing data in SQL Server. All you need to do is that execute following ALTER TABLE statements. Though, you need to specify NULL or NOT NULL constraint explicitly, depending upon your data.

Here is the SQL command you can use to increase the length of a VARCHAR column in SQL Server:


This command increases the length of title column of Books table to 432 characters. You can use the same command to increase the length of CHAR, NCHAR or NVARCHAR columns as well.

You can also use the same command to increase the length of any other type of the column as well. Similarly, you can also modify other properties of columns e.g. constraints, but beware with existing data e.g. if you try to make a  NULL column to NOT NULL then there would be many rows which will violate this constraint, hence, database will not allow you to change that property of column, until you remove those offending rows. You can also see here for an example of modifying column properties in SQL Server.

 Let's see an example of increasing length of  VARCHAR column in SQL Server.

Increasing Length of Existing VARCHAR Column in a Table

I have an Employee table in Test database, let's first see it's table definition. You can use the sp_help to see the table definition as shown below:

and now I will run the ALTER command to increase the length of its emp_name column, which is of VARCHAR type:

ALTER TABLE Customer ALTER COLUMN emp_name VARCHAR (100) ;

Here is the output of this command in SQL Server Management Studio, connected to SQL Server Express 2014 database:

You can see that emp_name column now has a length of  100 characters, increased from 50 characters it had before. See Microsoft SQL Server 2012 T-SQL Fundamentals to learn more about how to modify columns in the SQL Server database.

How to increase length of existing column in SQL Server

If you want to modify length of multiple columns you can run multiple ALTER command to change multiple columns, unfortunately, there is no way to increase length of multiple columns in single ALTER columns as following will throw syntax error:

ALTER TABLE Customer ALTER COLUMN customer_name VARCHAR (100), customer_address VARCHAR (90) ;
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'customer_address'

That's all about how to increase the length of existing VARCHAR column in SQL Server database. I have tried and tested the SQL query in Microsoft SQL Server 2008 edition and SQL Expression 2014 edition, but it should work on almost all database because it's ANSI SQL.

Further Learning
Introduction to SQL by Jon Flanders
Introduction to SQL Server
Head First SQL


Anonymous said...

How about increasing length of a NUMERIC column or changing data type of a column from DATE or TIME to DATETIME, can you use this trick?

shaik asheek said...

i'm getting invalid ALTER TABLE options

Javin Paul said...

Hello Shaik, where are you getting that error? which database? can you post your complete SQL query?

Post a Comment