How To Increase Length Of Existing Varchar Column Inwards Sql Server

You tin increment the length of a VARCHAR column without losing existing information inwards SQL Server. All you lot need to produce is that execute next ALTER TABLE statements. Though, you lot need to specify NULL or NOT NULL constraint explicitly, depending upon your data.

Here is the SQL ascendance you lot tin role to increment the length of a VARCHAR column inwards SQL Server:

ALTER TABLE Books ALTER COLUMN championship VARCHAR (432)

This ascendance increases the length of title column of Books tabular array to 432 characters. You tin role the same ascendance to increment the length of CHAR, NCHAR or NVARCHAR columns every bit well.

You tin too role the same ascendance to increment the length of whatever other type of the column every bit well. Similarly, you lot tin too modify other properties of columns e.g. constraints, exactly beware amongst existing information e.g. if you lot endeavour to brand a  NULL column to NOT NULL in addition to then in that place would endure many rows which volition violate this constraint, hence, database volition non allow you lot to alter that belongings of column, until you lot take those offending rows. You tin too encounter here for an instance of modifying column properties inwards SQL Server.

 Let's encounter an instance of increasing length of  VARCHAR column inwards SQL Server.

Increasing Length of Existing VARCHAR Column inwards a Table

I bring an Employee tabular array inwards Test database, let's showtime encounter it's tabular array definition. You tin role the sp_help to encounter the tabular array Definition every bit shown below:

in addition to immediately I volition run the ALTER ascendance to increment 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 ascendance inwards SQL Server Management Studio, connected to SQL Server Express 2014 database:

You tin encounter that emp_name column immediately has a length of  100 characters, increased from l characters it had before. See Microsoft SQL Server 2012 T-SQL Fundamentals to acquire to a greater extent than virtually how to modify columns inwards the SQL Server database.

If you lot desire to modify length of multiple columns you lot tin run multiple ALTER ascendance to alter multiple columns, unfortunately, in that place is no agency to increment length of multiple columns inwards unmarried ALTER columns every bit next volition throw syntax error:

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

That's all virtually how to increment the length of existing VARCHAR column inwards SQL Server database. I bring tried in addition to tested the SQL question inwards Microsoft SQL Server 2008 edition in addition to SQL Expression 2014 edition, exactly it should guide house almost all database because it's ANSI SQL.

Further Learning
Introduction to SQL
The Complete SQL Bootcamp
SQL for Newbs: Data Analysis for Beginners

Share this post