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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
up vote 27 down vote accepted

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.

share|improve this answer
I thought even the "Generate Script" button throws a warning that you are about to drop a table before even showing you the script. – Nick Chammas Aug 30 '11 at 18:17
It does throw up the error, but it doesn't actually run the script until you click on OK (or run or whatever) on the window with the script. You can copy the script and cancel the window which shows you the script. – mrdenny Aug 31 '11 at 8:10

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.


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.

share|improve this answer
Also great answer. But I could only pick one. Thanks! – Fernando Aug 30 '11 at 18:44

This is more of an answer to the Title than the question, but you can also decrease the size of an nvarchar column as long as you don't cause any data to be truncated.

If you attempt to make the column too small for existing values, an error will occur and the resize will not happen.

I've added a sqlfiddle to demonstrate. If you run it as is with the 2nd alter table commented out then itwill work, but if you swap the alter table statements then a data truncation error will occur.

share|improve this answer

Your Answer


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.