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.
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).
Smth. like this?
ALTER INDEX IndexName ON TableName
SET (ALLOW_ROW_LOCKS = OFF, ALLOW_PAGE_LOCKS=OFF)
BUT
see the answer of @gbn. Since it is not truly "the answer" - but the much more right approach to achieve your goal, than playing with the index options
