Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to 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.
Posted 21-Jan-13 7:09am
Edited 21-Jan-13 8:35am
v2

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

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: http://sqlserverplanet.com/ddl/alter-table-alter-column[^]
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
  Permalink  
Comments
Matt Maynard at 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ő at 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)

  Print Answers RSS
0 OriginalGriff 7,853
1 Sergey Alexandrovich Kryukov 7,107
2 DamithSL 5,604
3 Manas Bhardwaj 4,986
4 Maciej Los 4,790


Advertise | Privacy | Mobile
Web04 | 2.8.1411023.1 | Last Updated 21 Jan 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100