Here's one for the SQL Server gurus: if I set my SQL Server 2008 transaction isolation level to READ UNCOMMITTED, does that also affect the index pages?

E.g. using ISOLATION LEVEL READ UNCOMMITTED, what effect (if any) do the ALLOW_PAGE_LOCKS or ALLOW_ROW_LOCKS on an index have??

ALTER INDEX IX_FirstName ON Employee

I can't seem to find a definitive answer anywhere - the MSDN documentation on the transaction isolation levels really only talks about the data pages....

  • What problem are you solving? Maybe snapshot isolation is a better solution?
    – A-K
    Commented Feb 22, 2012 at 15:03
  • Just a question by a coworker :-) for now
    – marc_s
    Commented Feb 22, 2012 at 15:35

1 Answer 1


No. It doesn't affect anything regarding the internal management within SQL Server. You're setting that connection for you, for your queries. SQL Server manages it's own locking it's own way.

Why would you turn off page and row locking on an index? You're more likely to see more severe locking than if you let SQL Server manage that index as it sees fit. By setting both those values to OFF, you just told SQL Server to take a table lock out. Considering you're also messing with read_uncommitted, I'll bet you don't want that.

If you're asking does read_uncommitted allow you to get dirty reads on indexes, yes. It does.

  • 1
    OK, thanks - that sort of / partially confirms my gut feeling :)
    – marc_s
    Commented Feb 22, 2012 at 14:50

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.