support for nolock in SQL Azure

Question

Does anyone have an idea as to why SQL Azure does not support with nolock SQL statements. Does Microsoft has any plans of supporting this in the future?

Answer 1

I tried running a nolock query on SQL azure and it works. That got me thinking that the statement is ignored because technically you don't have the option to set the database option (READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION). I'm guessing this is the reason but I'll try and do some more digging.

Note: this is an Azure limitation.

You can find more information here:

Both the READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION database options are set to ON in SQL Azure Database. Because SET in the ALTER DATABASE Transact-SQL statement is not supported, these database options cannot be changed. For more information on row versioning-based isolation levels, see Understanding Row Versioning-Based Isolation Levels.

Update: after a quick discussion with two über SQL experts, the solution appears to be to use a query like:

set transaction isolation level READ UNCOMMITTED
select * from myTestTable

So for every batch, you need to specify the isolation level.