Using Snapshot Isolation level in a SQL Server View

Question

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?

Answer 1

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

Answer 2

To activate Snapshot isolation level on a database, use this code:

ALTER DATABASE [your DB] SET READ_COMMITTED_SNAPSHOT ON;
ALTER DATABASE [your DB] SET ALLOW_SNAPSHOT_ISOLATION ON;

Your views and tables will use it automaticaly when ON.

More info [https://technet.microsoft.com/en-us/library/ms175095(v=sql.105).aspx][1]

• Alex Shelemin: Thanks for your answer! I should've been more articulate: I'm not talking about Read Committed Snapshot, but only about Snapshot Isolation, which should be explicitly set on a statement/procedure level. I understand setting Read Committed will affect all queires to a database