Does sp_indexoption cause index rebuilds?

Question

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?

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
set
(
    allow_row_locks = on
);
go

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