ROLLBACK TRANSACTION
Execute the query in the second query window again.The result is different because the transaction was rolled back.

If you recollect, before we set the transaction isolation level, the default behavior was to wait till the other transaction completes. This is known as the transaction isolation level READ COMMITTED. Instead of relying on the default behavior, you can explicitly set the transaction isolation level to READ COMMITTED.Let us repeat the concurrent updates that way then. This is what you do in the first query editor window:

BEGIN TRANSACTION

UPDATE Employee
SET city = 'Delhi'
WHERE empid = 1

And this is what you do in the second query editor window:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SELECT empid, city FROM Employee
WHERE empid = 1

As expected, this query is blocked. Let us roll back the first transaction.

ROLLBACK TRANSACTION

This will unblock the second query.

SNAPSHOT
Remember what had happened when we set the transaction isolation level to READ UNCOMMITTED? We were getting the data after the update, even if it was not committed. What if we would want the data before the update? We would set the transaction isolation level to SNAPSHOT. You would first need to configure the database to allow SNAPHOT isolation. You can execute the following statement in any of the query windows. Only take care not to run this inside a transaction boundary.

ALTER DATABASE payroll
SET ALLOW_SNAPSHOT_ISOLATION ON

My database name is payroll; substitute whatever is the name of your database. Now let me attempt the same transaction in the first query window.

BEGIN TRANSACTION
UPDATE Employee
SET city = 'Delhi'
WHERE empid = 1

Set the transaction isolation level to SNAPSHOT in the second query window.

SET TRANSACTION ISOLATION LEVEL SNAPSHOT
SELECT empid, city FROM Employee
WHERE empid = 1

The query runs without blocking and shows the data as it was before the modification. But if you choose the commit the transaction in the first query window and read the data again, you will get the new values.

COMMIT TRANSACTION
Either in the case of your transaction level being READ UNCOMMITED or SNAPSHOT, you say you want to read the data before the other transaction has finished modification. You can take the choice of whether you want to see the data as it was before the modification or after the modification, there is still no guarantee the data will remain the same after the transaction completes, because it may either commit or rollback.