Create a snapshot for a transaction?

PostgreSQL has a feature to create a snapshot for a transaction using:

SET TRANSACTION SNAPSHOT snapshot_id

Does SQL Server have a similar feature?

I have an application program that uses a SQL Server backend. It invokes two read-only queries Q1 and Q2 using separate connections. This means they do not execute in the same transaction. I need to ensure both connections see the database in the same precise state, as would be afforded via the SET TRANSACTION SNAPSHOT statement in PostgreSQL.

How can I ensure both Q1 and Q2 see the database in a consistent state, identical to both connections?

So there is a way to do this using a couple of features that have probably never been used together in the real world: snapshot isolation and bound sessions.

Bound Sessions in particular is an extremely old, obscure and deprecated feature (although like many deprecated features it may never actually be removed).

Anyway the procedure sp_bindsession allows a session to participate in a transaction started by a different session. And if both sessions are in SNAPSHOT isolation, then they will share a point-in-time consistent view of the database.

So setup:

create database testdb
alter database testdb set allow_snapshot_isolation on
go
use testdb
drop table if exists t
create table t(id int)

Then from one session:

--session 1
use testdb 
if @@trancount > 0 rollback

set transaction isolation level snapshot
begin transaction

declare @token varchar(255)
exec sp_getbindtoken @token out 
select @token 

insert into t(id) values (1)

and from another session, after copying and pasting the bind token:

--session 2
use testdb
go

set transaction isolation level snapshot
declare @token varchar(255) = '7-`L>5B0-1[OgFb4FVTWU]5---/=AM--'

select @@trancount --returns 0

exec sp_bindsession @token 

select @@trancount --returns 1

go

select * from t --returns a row

rollback --rolls back the transaction started in the other session

go

select * from t --no rows

But any real-world scenario, you should probably either use Multiple Active Result Sets and run the queries in the same session, or create a Database Snapshot

StackExchange Database Administrators Q#143610, answer score: 4