Table vs. row locks when inserting from select

Question

4.00/5 (1 vote)

See more:

SQL-Server-2005

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:

SQL

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 6:09am

Matt Maynard

Updated 21-Jan-13 7:35am

Zoltán Zörgő

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

**Zoltán Zörgő** · Accepted Answer · 2013-01-21T07:41:00

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

Posted 21-Jan-13 7:41am

Zoltán Zörgő

Comments

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.