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 y'all ask to produce is that execute next ALTER TABLE statements. Though, y'all ask to specify NULL or NOT NULL constraint explicitly, depending upon your data.

Here is the SQL ascendency y'all tin purpose to increment the length of a VARCHAR column inwards SQL Server:

ALTER TABLE Books ALTER COLUMN championship VARCHAR (432)

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

You tin every bit good purpose the same ascendency to increment the length of whatever other type of the column every bit well. Similarly, y'all tin every bit good modify other properties of columns e.g. constraints, simply beware amongst existing information e.g. if y'all endeavor to brand a  NULL column to NOT NULL so at that spot would last many rows which volition violate this constraint, hence, database volition non allow y'all to modify that holding of column, until y'all take away those offending rows. You tin every bit good run across here for an instance of modifying column properties inwards SQL Server.

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

Increasing Length of Existing VARCHAR Column inwards a Table

I convey an Employee tabular array inwards Test database, let's starting fourth dimension run across it's tabular array definition. You tin purpose the sp_help to run across the tabular array Definition every bit shown below:

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

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

If y'all desire to modify length of multiple columns y'all tin run multiple ALTER ascendency to modify multiple columns, unfortunately, at that spot 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 close 'customer_address'

That's all virtually how to increment the length of existing VARCHAR column inwards SQL Server database. I convey tried in addition to tested the SQL interrogation inwards Microsoft SQL Server 2008 edition in addition to SQL Expression 2014 edition, simply it should operate on 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