Will increasing a column (nvarchar) width necessarily drop the table?
In other words, can the width be changed in a production environment with active users?
I figured that if the size is increasing (as opposed to decreasing) this wouldn't be a problem.
If you're doing it through a T-SQL statement such as below, then no table drop will occur and you can safely do it in a production environment:
alter table <table> alter column <column> nvarchar(biggernumber) [not] null
If you do it through the SSMS Design Table GUI, it will depend on what script it decides to use to implement the change. Sometimes it will insert data into a temporary table, drop the original table, create a new version of that table, and insert it back into the new one. An easy way to find out what it will do is to click the "Generate Script" button and look at the T-SQL it plans on executing.
Increasing the column width of a nvarchar column won't require a table drop. Neither would any ALTER TABLE operation. For details about restrictions when changing table or column properties you can read up on the ALTER TABLE statement.
I copied the most relevant parts from the documentation below:
Changing the Size of a Column
You can change the length, precision, or scale of a column by specifying a new size for the column data type in the ALTER COLUMN clause. If data exists in the column, the new size cannot be smaller than the maximum size of the data. Also, the column cannot be defined in an index, unless the column is a varchar, nvarchar, or varbinary data type and the index is not the result of a PRIMARY KEY constraint. See example P.
Locks and ALTER TABLE
The changes specified in ALTER TABLE are implemented immediately. If the changes require modifications of the rows in the table, ALTER TABLE updates the rows. ALTER TABLE acquires a schema modify lock on the table to make sure that no other connections reference even the metadata for the table during the change, except online index operations that require a very short SCH-M lock at the end. In an ALTER TABLE…SWITCH operation, the lock is acquired on both the source and target tables. The modifications made to the table are logged and fully recoverable. Changes that affect all the rows in very large tables, such as dropping a column or adding a NOT NULL column with a default, can take a long time to complete and generate many log records. These ALTER TABLE statements should be executed with the same care as any INSERT, UPDATE, or DELETE statement that affects many rows.
