Sql-server – Alternatives to sp_indexoption for Row Locks and Page Locks in SQL Server 2008

deadlocklockingsql serversql-server-2008

Are there better alternatives to setting the Row Locks and Page Locks in SQL Server 2008?

The case here is to set the row and page locks to false on the table. The lock is in effect during a series of insert, update and delete actions on the table which are run in a batch.

Best Answer

If I understand you correctly, a better way is to use TABLOCK hint to force a table level lock in the query that needs it (example: delete from a heap)

UPDATE SomeTable WITH (TABLOCK)
SET ...


INSERT SomeTable WITH (TABLOCK)
...

Personally, I wouldn't set it globally in the CREATE INDEX statement...

Also, the MSDN page for sp_indexoptions states

This feature will be removed in the next version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Instead, use ALTER INDEX (Transact-SQL).

Related Question