I want to switch AllowRowLocks=ON on a number of indexes. Some of those indexes are on large tables, and I don't really want to rebuild or reorganize the index.

It is not clear from the MSDN article whether or not the index will be rebuilt.

As a secondary related part of this question, the MSDN article states that the use of this is being phased out and shouldn't be used anymore:

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

Looking at the ALTER INDEX, it seems you are forced to rebuild or reorganize. Have I understood that correctly? Through ALTER INDEX can I just reset this row lock option?

1 Answer 1


Under the covers sp_indexoption just does an ALTER INDEX as well.

My recommendation would be to forget about sp_indexoption and just utilize ALTER INDEX:

alter index IX_YourIndex
on dbo.YourTable
    allow_row_locks = on

As noted in the BOL reference for ALTER INDEX, when you specify just the set options:

Specifies index options without rebuilding or reorganizing the index

  • 3
    Would love to +1 twice! Commented Aug 22, 2014 at 13:33
  • Awesome, Thank you. The syntax on that MSDN page is rather meaty!
    – Rebecca
    Commented Aug 22, 2014 at 14:00
  • @Junto Glad to help :-) And yes for some of the more "robust" references they can be quite daunting with all of the syntax and information. Commented Aug 22, 2014 at 14:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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