Change mssql isolation level to read uncommitted data

Sometimes you are debugging an application and want to query the database while the database connection still has not committed (or rollbacked) the transaction. So you fire up the query analyzer just to find out that you cannot see the modifications yet (ofcourse). Today a collegue of mine (Frank Bakker) found out that it is possible to read uncommitted data by altering the isolation level on a connection.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

When you have executed this statement you can now see all modifications that are still in a transaction. Isnt that neat or what?

The thing is that I used this some years ago in some functionality that generates reports based on website visits. The query did a select statement and it wouldn't be bothered if some additional records would have beed added to the table. You can get this behaviour by adding the keyword NOLOCK like the following example.

SELECT count(*)
FROM MyTable NOLOCK
GROUP BY MyCoolColumn

So NOLOCK and SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED are the same but the first (NOLOCK) is usualy best option as it is directly visible that the query needs a different isolation level. Setting the isolation level seperately can ofcourse increase readability is your query does a lot of joins though.

You can also do this in MySql in almost the same syntax as shown below.

SELECT count(*)
FROM MyTable(nolock)
GROUP BY MyCoolColumn

Happy 'reading'...