Using ROWLOCK in an INSERT statement (SQL Server)

Would it be wise to use ROWLOCK on an insert statement that is copying large amounts of data and inserting it into the same table?

Ex)

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

Does anybody have recommendations on how to improve thi开发者_StackOverflows statement, as I see when SQL Server gets busy it will end in Timeout Query returned for SQL Server.

You're probably better off storing the sub-query result in a temporary table before and insert that.

Well, it is not possible to use WITH (NOLOCK) table hint with INSERT statements. See https://learn.microsoft.com/en-us/sql/t-sql/statements/insert-transact-sql#arguments

If there is only a single process or application that is copying the data within the same table at any given time, then various transaction isolation levels will not do much for you. They are meant to isolate (separate) different transactions, and have no effect on a single one.

Moreover, it is typically best to delegate the query optimization to SQL Server as all the necessary elements of the query are known, and there are - I assume - no other competing queries against the same table, which could have messed up the optimized query execution plan.

If you are inserting a large amount of data and you have readers/writers problems (lock, timeouts) you should probably split your insert into pieces (top 100 or something) until you complete all data. If you don't, even if you declare rowlock, lock escalation will probably occur, in which case SQL server will acquire a table lock for the time you are inserting data.

Another good option would be to use SNAPSHOT isolation which would be perfect if you have plenty of space available. For the first option read hereQ

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

If you are inserting large amount of data, for example 1000 rows or more, then you can consider using temp table, or table variables. First insert the rows into temp table or variables, then perform insert into final_table select * from temp table will work very well. If you need more rows, then simply put the insert inside a cursor and iterate say every 1000 rows until the total number of rows are completed.

For more complex insert, if you need to preserve the identity keys, or use them as reference keys in other tables, you can get put the entire store procedure in a transaction, and count the last used identity key from the final table, and use that as the first value of the identity key for temp table, or set identity turned off.