Peek into the database during your unit test

Have you ever had the following problem?

You are debugging a unit test which has just inserted some records into your database. While the unit test is waiting on a breakpoint you want to see the result of the insert in the database. You start up SQL Query Analyzer type 'select * from customers' and hit <F5>. What happens? If you are lucky you get to see the table as it was before you started the unit test, if you are unlucky your query just 'hangs'. Now why is that?

Anyone who understands a little bit about transactions knows this must be because the insert is done inside a transaction which has not yet been committed. Therefore the result of the insert is isolated from any other database process or a table lock might even prevent you from reading the table at all.

When I ran into this (again) today I started thinking about it and found a solution which in fact is very simple. Before running your select * from customers you execute the command:

SET TRAN ISOLATION LEVEL READ UNCOMMITTED

The trick is that by setting the isolation level to 'read uncommitted' you can read the data that was inserted by the unit test code even though there might be table locks and the changes might eventually even get rolled back (which is usually the case in a unit test).