Click here to Skip to main content
15,843,200 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
We have to change the size of a nvarchar field in a table in our SQL Server 2005 db (table A), and because of the amount of data in the table this modification will cause some serious access problems. To remedy this, I've created a table B with the same schema, except for the larger nvarchar field. Once I copy data from A to B, I will delete A and rename B.

My question is about row versus table locks. If I do this:
INSERT INTO B ([field1],[field2])
SELECT [field1],[field2] FROM A

Will I have a table level lock on A while I fill B, or will I only experience row-level locks? If I have a table lock, can I avoid it by saying 'SELECT [field1],[field2] FROM A with (nolock)'?

I'll also add that I have access to the service that fills A, and can shut it down while I run the query so that I don't lose data.
Updated 21-Jan-13 8:35am

1 solution

As I see, you don't need to lock anything.

As you stated, you can stop the service doing dml. Do it for sure!
A) Can't you change datatype without using a second table? See:[^]
B) If some data transformation is needed, you could add a new field to the same table, and use UPDATE to fill it up; than delete original field; than rename new field
C) You can still use the approach drafted by you without lock
Share this answer
Matt Maynard 21-Jan-13 13:52pm    
A) As for the datatype, it's going from nvarchar(75) to nvarchar(256). When I try to save the change in design view in SQL Server Management Studio, I get a warning: "Saving Definition Changes to tables with large amounts of data could take a considerable amount of time. While changes are being saved, table data will not be accessible." The table has 3.6m rows, give or take.

B) This is probably the better solution, since the dataset takes up 691MB; not that available space is an issue, but writing out the entire table when I'm only trying to modify one column is probably a bit excessive.
Zoltán Zörgő 21-Jan-13 15:21pm    
A) You can test it by making a replica of the database on the same server, or on an other with similar resources. If the outage is acceptable, than you can perform it also on the live one.
B) Yes, this is a simple one. I have used it myself also, mainly in situations where I had to make some changes on the data itself too.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900