Read both underlying and uncommitted transaction data

Using SQL Server from .NET, is it possible (using the same or separate connections) to read both the underlying data as well as the as-yet uncommitted changes from another separate connection?

For example:

I have Connection1, which starts a transaction and inserts a record with Id == 1 into a table but doesn't commit it

From Connection2, I would like to read the table without that row existi开发者_高级运维ng

From Connection2 or Connection3, I would like to read the table with the row existing.

Yes, you need to enable dirty reads aka READ UNCOMMITTED

Edit:

To read both sets of data you'd need a combination of "snapshot isolation" in one connection and "read uncommitted" in another.

YMMV. It isn't something...

1. I've tried
2. See a use for
3. I'd have confidence in

As you know, you can review uncommitted data within the current session like so:

CREATE TABLE TestTable
(
    ID int not null
);

INSERT INTO TestTable(ID) values(1);
INSERT INTO TestTable(ID) values(2);
INSERT INTO TestTable(ID) values(3);

SELECT * FROM TestTable;

BEGIN TRANSACTION

    INSERT INTO TestTable(ID) values(4);
    INSERT INTO TestTable(ID) values(5);

    --Although the transaction has not commited you can still see the records inserted from within the current sessions scope.
    SELECT * FROM TestTable;


COMMIT TRANSACTION

DROP TABLE TestTable;

Now suppose you where to open another connection that you wanted to be able to see this uncommited data. You could do so at the query level by using the NOLOCK query hint. For example:

SELECT * FROM TestTable WITH(NOLOCK)