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.

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?

share|improve this question

1 Answer 1

up vote 7 down vote accepted

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

share|improve this answer
3  
Would love to +1 twice! –  Aaron Bertrand Aug 22 at 13:33
    
Awesome, Thank you. The syntax on that MSDN page is rather meaty! –  Junto Aug 22 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. –  Thomas Stringer Aug 22 at 14:03

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.