Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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

What happens if I change a column from nvarchar(2) to nvarchar(10) or a column from char(2) to nvarchar(10). Does this lock, reorganize the table or does this work without any impact or downtime?

I tried to find what happens if I alter a column, but I did not find anything. Its a large table with one or two columns to be changed. Its about a MS SQL Server 2008 R2.

share|improve this question

The easiest way to find out would be to use a test environment create a dummy table and some dummy records apply the change the talk about and then trace the actions through profiler.

OR Change the table in the table designed in SSMS and then use the 'Generate Change script' button. When I change my column from Char(2) to NVARCHAR(10). The generate change script produces the following:

   CREATE TABLE dbo.Tmp_t2
    id int NULL,
    c1 nvarchar(50) NULL,
    c2 nvarchar(2) NULL
    )  ON [PRIMARY]
     EXEC('INSERT INTO dbo.Tmp_t2 (id, c1, c2)
        SELECT id, CONVERT(nvarchar(50), c1), c2 FROM dbo.t2 WITH (HOLDLOCK TABLOCKX)')
EXECUTE sp_rename N'dbo.Tmp_t2', N't2', 'OBJECT' 
share|improve this answer

If you're using the ALTER commands then it will create another table, copy the existing data to the new table, drop the old table when finished, and then rename the new table with the old table name. I don't think it is best practice to do it during live database but there are methods to deal with production data.

share|improve this answer
Are you sure of this? I read something about compying a table, but that was for mysql. The article is from 2004. I assume in SQL Server 2005 and SQL 2008 R2 Enterprise got some improvements. – Uwe Dec 22 '10 at 17:42 Look at the Remarks section and Parallel Plan execution. – Ishmael Dec 22 '10 at 18:43
and which part of that section says that copying the table is how it is done? – SqlACID Dec 24 '10 at 0:24
I think that the object_id would change if the table is copied. Didn't see this. – bernd_k Dec 29 '10 at 18:55

No other operation(like insert update delete) can be done when data type of table column is modifying, whole table will be lock. also can not be select with NOLOCK.

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.