Does the transaction isolation level affect indices as well?

Question

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
SET (ALLOW_PAGE_LOCKS=OFF, ALLOW_ROW_LOCKS=OFF)

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

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.