Changing the maximum length of a varchar column?

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

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.