Pages

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:

ALTERTABLE Books ALTER COLUMN title VARCHAR (432)

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:

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.

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:

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.