Have you ever tried to reproduce a bug in an application, and in the middle of your debugging process, you reeeeeally would have liked to be able to see the difference between some data from both before and after the bug occurred in order to compare data?
Oh boy, have I just stumbled upon a great debugging trick for this... and Imma share it with ya!
Now, to do this, you'll need to have RCSI (Read Committed Snapshot Isolation) enabled on your database.
What RCSI does is that it enables optimistic locking. Long story short; RCSI causes every single update or delete to store the old data in TempDB until the update or delete has committed its transaction.
Why? Because now other sessions can still read from the same tables and rows as you are updating, instead of causing a block until you have committed the change. It allows other sessions to read, not from the live data, but from the old data (before the change) that is stored in the TempDB.
For the full rundown, just search for RCSI. There's more info about it if you want to know more.
What I am about to show you is done in two different database sessions in SSMS, but I find this mostly useful as a compliment of a SSMS-session to an active debugging session of you application's code.
We start of by checking the value of a random user from our Users table.
Say hello to Alan, with user id 9731318.
Now, say we want to update Alan's name... to George!
Since a ran this update within a transaction, the old displayname (Alan) is now stored in TempDB and if any other session is trying to get the displayname of user id 9731318, they will still retreive "Alan". If the same session that just set the new name tries to get the name of this user id, he will obviously get "George", as he has updated this value.
BUT there is actually still a way to get the current data in the other session!
Yup, you guessed it. By using nolock. Nolock will still override any present locks on the selected objects, and therefore not actually look into TempDB for the old data.
This has been incredibly helpful to me when I've been debugging an application to find when a change is occurring.
For instance, if I am trying to quickly track down a bug where displayname (and possible more things) is being incorrectly changed, I don't actually have to keep track of the variables in my debugger to see what is changing and when, but I can just have a second window open with SSMS and run a super simple query with and without nolock to quickly see the differences that the bug caused.