Using Rowlock when inserting data helps..?

Question

I want to use ROWLOCK option in sql server when inserting data into table. Does it helps in any manner. Will it help me to decrease blocking in my database?

Answer 1

Using ROWLOCK is a hint to the SQL Server query optimizer that you know better than it does about this SQL query. It basically says lock the table/view rows affected by the FROM clause of your query rather than escalating to a page or table lock.

If applied by an experienced developer or administrator, it might help with blocking in very specific circumstances. But in general I would leave the locking strategy up to the query optimizer unless you've clearly identified that locking isn't being done efficiently for a specific query.

There is a good article on lock escalation for SQL Server 2005, though I believe there have been some changes in SQL Server 2008. There is a decent article on locking in SQL Server 2008.