Using ROWLOCK in an INSERT statement (SQL Server)

Question

Would it be wise to use ROWLOCK in an insert statement that copies large amounts of data and inserts them into the same table?

Ex)

INSERT INTO TABLE with (rowlock) (id, name) 
   SELECT newid, name 
   FROM TABLE with (nolock) 
   WHERE id = 1

Does anyone have any recommendations on how to improve this statement, as I see when SQL Server gets busy, it will complete the Timeout query returned for SQL Server.

Answer 1

You are probably better off storing the result of the subquery in the temporary table until and insert this.

Answer 2

, / (, -), , , (100 - ), . , rowlock, , , SQL- , .

- SNAPSHOT, , . Q

http://support.microsoft.com/kb/323630

Answer 3

, 1000 , temp . temp , final_table select * temp-, . , 1000 , .

, , , temp, .

Answer 4

, WITH (NOLOCK) INSERT. . https://docs.microsoft.com/en-us/sql/t-sql/statements/insert-transact-sql#arguments

If there is only one process or application that copies data in the same table at any given time, then different levels of transaction isolation will not help you. They are intended for isolation (separation) of various transactions and do not affect one.

In addition, it is generally best to delegate SQL Server query optimization, since all the necessary query elements are known, and there are - I assume - no other competing queries for a single table that could ruin the optimized query execution plan.