Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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
add comment

2 Answers

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
share|improve this answer
add comment

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

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
    
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 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
add comment

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.