The SQL standard specifies a default isolation level of 3. To enforce this level, Transact-SQL provides the set transaction isolation level command. For example, you can make level 3 the default isolation level for your session as follows:
set transaction isolation level 3
If the session has enforced isolation level 3, you can make the query operate at level 1 using noholdlock, as described below.
If you are using the Adaptive Server default isolation level of 1, or if you have used the set transaction isolation level command to specify level 0 or 2, you can enforce level 3 by using the holdlock option to hold shared locks until the end of a transaction.
The current isolation level for a session can be determined with the global variable @@isolation.