6

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.

Kyle Brandt
  • 83,619
  • 74
  • 305
  • 448
kcode
  • 1,825
  • 4
  • 19
  • 21

3 Answers3

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
Gethyn Ellis
  • 21
  • 1
0

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

Ishmael
  • 71
  • 2
  • 9
  • 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. – kcode Dec 22 '10 at 17:42
  • http://msdn.microsoft.com/en-us/library/ms190273.aspx. 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 00: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
0

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.

Piyush Sachan
  • 1