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.

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

2 Answers 2

up vote 17 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.

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.

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

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.