Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Jan 10 '12 at 13:28

This question came from our site for professional and enthusiast programmers.

2 Answers 2

up vote 3 down vote accepted

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).

share|improve this answer

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.