Changing the maximum length of a varchar column?

sql sql-server sql-server-2008 tsql varchar
Michael A picture Michael A · Jan 12, 2012 · Viewed 349.2k times · Source

I'm trying to update the length of a varchar column from 255 characters to 500 without losing the contents. I've dropped and re-created tables before but I've never been exposed to the alter statement which is what I believe I need to use to do this. I found the documentation here: ALTER TABLE (Transfact-SQL) however I can't make heads or tails of it.

I have the following so far (essentially nothing unfortunately):

alter table [progennet_dev].PROGEN.LE
alter column UR_VALUE_3

How do I approach this? Is there better documentation for this statement out there (I did some searches for an example statement but came up empty)?

Answer

Martin Smith picture Martin Smith · Jan 12, 2012

You need 

ALTER TABLE YourTable ALTER COLUMN YourColumn <<new_datatype>> [NULL | NOT NULL]

But remember to specify NOT NULL explicitly if desired. 

ALTER TABLE YourTable ALTER COLUMN YourColumn VARCHAR (500) NOT NULL;

If you leave it unspecified as below...

ALTER TABLE YourTable ALTER COLUMN YourColumn VARCHAR (500);

Then the column will default to allowing nulls even if it was originally defined as NOT NULL. i.e. omitting the specification in an ALTER TABLE ... ALTER COLUMN is always treated as.

ALTER TABLE YourTable ALTER COLUMN YourColumn VARCHAR (500) NULL;

This behaviour is different from that used for new columns created with ALTER TABLE (or at CREATE TABLE time). There the default nullability depends on the ANSI_NULL_DFLT settings.

Related questions

How to run a more than 8000 characters SQL statement from a variable?

I can use the following code for tiny little queries: DECLARE @sql VARCHAR(8000) SET @sql = 'SELECT * FROM myTable' Exec @sql The above method is very useful in order to maintain large amounts of code, especially when we need to make …

Read More
How can I get column names from a table in SQL Server?

I want to query the name of all columns of a table. I found how to do this in: Oracle MySQL PostgreSQL But I also need to know: how can this be done in Microsoft SQL Server (2008 in my case)?

Read More
How can I select the first day of a month in SQL?

I just need to select the first day of the month of a given datetime variable. I know it's quite easy to do using this kind of code: select CAST(CAST(YEAR(@mydate) AS VARCHAR(4)) + '/' + CAST(MONTH(@mydate) …

Read More