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?
Best Answer
If you are increasing it to
varchar(100 - 8000)
(i.e. anything other thanvarchar(max)
) and you are doing this through TSQL rather than the SSMS GUIand not altering column nullability from
NULL
toNOT NULL
(which would lock the table while all rows are validated and potentially written to) or fromNOT NULL
toNULL
in some circumstances then this is a quick metadata only change. It might need to wait for aSCH-M
lock on the table but once it acquires that the change will be pretty much instant.One caveat to be aware of is that during the wait for a
SCH-M
lock other queries will be blocked rather than jump the queue ahead of it so you might want to consider adding aSET LOCK_TIMEOUT
first.Also make sure in the
ALTER TABLE
statement you explicitly specifyNOT NULL
if that is the original column state as otherwise the column will be changed to allowNULL
.