Does resizing a column lock the table?

Question

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.

Answer 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

Answer 2

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.

http://www.sqlmag.com/article/tsql3/resizing-a-column-in-a-large-table.aspx

Answer 3

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.