Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a MS SQL Server 2008 R2 DB server currently in use with a production app.

A new enhancement to the app now requires a varchar(100) column in a table to be increased in length.

Can the length of this existing column in the prod DB be increased without affecting the current data?

Does this change have to be completed during off-hours to avoid disruption of service?

share|improve this question

1 Answer 1

up vote 15 down vote accepted

If you are increasing it to varchar(100 - 8000) (i.e. anything other than varchar(max)) and you are doing this through TSQL rather than the SSMS GUI

ALTER TABLE YourTable ALTER COLUMN YourCol varchar(200) [NOT] NULL

and not altering column nullability from NULL to NOT NULL (which would lock the table while all rows are validated) then this is a quick metadata only change. It might need to wait for a SCH-M lock on the table but once it acquires that the change will be pretty much instant.

Make sure in the ALTER TABLE statement you explicitly specify NOT NULL if that is the original column state as otherwise the column will be changed to allow NULL.

share|improve this answer
    
Thanks for the quick answer, was just checking. It was nearly instant. –  anataliocs Mar 14 '12 at 16:46

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.