Does resizing a column lock the table?

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.

Solution 1:

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]
GO
ALTER TABLE dbo.Tmp_t2 SET (LOCK_ESCALATION = TABLE)
GO
IF EXISTS(SELECT * FROM dbo.t2)
     EXEC('INSERT INTO dbo.Tmp_t2 (id, c1, c2)
        SELECT id, CONVERT(nvarchar(50), c1), c2 FROM dbo.t2 WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.t2
GO
EXECUTE sp_rename N'dbo.Tmp_t2', N't2', 'OBJECT' 
GO
COMMIT