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 execute the 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:

ALTER TABLE Books ALTER COLUMN title VARCHAR (432)

This command increases the length of the title column of the 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 column as well.

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

And, If you are new to the SQL world, it's better to start with a comprehensive SQL course like The Complete SQL Bootcamp course by Jose Portilla on Udemy. That will help you to learn SQL better and quicker, and these kinds of articles will also make more sense once you have some SQL knowledge under your belt.

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

Increasing Length of Existing VARCHAR Column in a Table

I have an Employee table in the Test database, let's first see its 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 the emp_name column now has a length of  100 characters, increased from 50 characters it had before. You can further see Microsoft SQL for Beginners to learn more about how to modify columns in the SQL Server database and other SQL Server fundamentals. 

If you want to modify the length of multiple columns you can run multiple ALTER command to change multiple columns, unfortunately, there is no way to increase the length of multiple columns in single ALTER columns as the 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 the existing VARCHAR column in the SQL Server database. I have tried and tested the SQL query in Microsoft SQL Server 2008 edition and SQL Expression 2014 and 2019 edition, but it should work on almost all databases because it's ANSI SQL.

Further Learning
Introduction to SQL

Microsoft SQL for Beginners 
SQL for Newbs: Data Analysis for Beginners

Other related SQL queries, Interview questions, and articles:

• How to join three tables in one single SQL query (solution)
• Write a SQL query to find all table names on a database in MySQL (solution)
• 5 Courses to learn Database and SQL Better (courses)
• Difference between the Primary and Candidate key in the table? (answer)
• Top 5 Websites to learn SQL online for FREE? (resource)
• Top 5 Courses to learn Microsoft SQL Server in-depth (courses)
• How to migrate SQL queries from Oracle to SQL Server 2008? (answer)
• 5 Courses to learn Oracle and Microsoft SQL Server database (courses)
• Difference between Self and Equi Join in SQL? (answer)
• 4 Free Books to learn Microsoft SQL Server database (books)
• Top 5 Websites to learn SQL online for FREE? (websites)
• What is the difference between UNION and UNION ALL in SQL? (answer)
• Write a SQL query to copy or backup a table in MySQL (solution)
• How do you find the duplicate rows in a table on a database? (solution)
• 5 Advanced SQL books to level up your SQL skills (books)
• The real difference between WHERE and HAVING clause in SQL? (answer)
• 5 Free Courses to learn Database and SQL (free courses)
• Difference between the Unique and Primary key in the table? (answer)
• Top 5 Courses to learn PostgreSQL in-depth (courses)
• 5 Free Courses to learn Oracle and SQL Server? (courses)
• Top 5 Courses to learn MySQL Database for Beginners (Courses)
• What is the difference between View and Materialized View in Database? (answer)
• Difference between clustered and non-clustered indexes in SQL? (answer)
• 5 Free Courses to learn T-SQL and SQL Server for Beginners (Courses)
• 5 Free Courses to learn T-SQL and SQL Server for Beginners (Courses)
• Difference between Primary and Foreign keys in the table? (answer)

Thanks for reading this article, if you like this SQL ALTER command tutorial, then please share it with your friends and colleagues. If you have any questions or feedback, then please drop a note.

P.S. - If you are new to the Data and SQL world and interested in learning Database and SQL and looking for some free resources to start your journey, then you can also take a look at this Introduction to Databases and SQL Querying [FREE] course on Udemy to kick-start your learning.