SET TRANSACTION ISOLATION LEVEL
{ READ COMMITTED
| READ UNCOMMITTED
| REPEATABLE READ
| SERIALIZABLE
}
The READ UNCOMMITTED option allows dirty reads.
It is the same as using the NOLOCK or READUNCOMMITTED table hint.
It does not create locks nor does it honor any locks.
The data you read while it is in effect may be in the process of being changed.
The REPEATABLE READ option additionally disallows phantom rows.
Shared locks remain in place for the duration of the transaction.
The outside process can still insert rows that will be picked up by your second SELECT.
The SERIALIZABLE option is the most restrictive.
It additionally prevents other processes from inserting rows between your first and second SELECTs.
If the INSERT will make no difference to your two SELECTs, it is allowed; otherwise, it is not.
Reference:
Advanced Transact-SQL for SQL Server 2000