sql-serverviewsnapshot-isolation

Using Snapshot Isolation level in a SQL Server View

After doing some research, I can't really find a way to use a Snapshot Isolation level in a SQL Server view.

Is there some way to achieve it, or if it is indeed impossible, what is the best way to approach a situation, when I'd like my views to not block writers, but still avoid using NOLOCK hint on them?

Solution

  • I think you can't force the view to always use Snapshot isolation (Snapshot isolation doesn't have query hints because it's on the transaction level, not the query level). The callers of the view would have to set the isolation themselves:

    SET TRANSACTION ISOLATION LEVEL SNAPSHOT
SELECT * FROM dbo.YourView