SQL Server 2017 has a couple new stored procedures:

 - sp_refresh_single_snapshot_view – input param for @view_name nvarchar(261), @rgCode int
 - sp_refresh_snapshot_views – input param for @rgCode int

And new entries in sys.messages:

 - 10149 – Index that has SNAPSHOT_MATERIALIZATION cannot be created on view ‘%.*ls’ because view definition contains memory-optimized table(s).
 - 10642 – SNAPSHOT_MATERIALIZATION cannot be set for index ‘%.*ls’ on ‘%.*ls’ because it is only applicable to indexes on views.
 - 10643 – SNAPSHOT_MATERIALIZATION cannot be set for ‘%.*ls’ on ‘%.*ls’ because it is only applicable to clustered indexes on views.
 - 10648 – SNAPSHOT_MATERIALIZATION cannot be set for partitioned index ‘%.*ls’ on ‘%.*ls’.
 - 10649 – Nonclustered index ‘%.*ls’ cannot be created on ‘%.*ls’ that has clustered index ‘%.*ls’ with SNAPSHOT_MATERIALIZATION.
 - 10650 – Refresh of snapshot view(s) requires snapshot isolation to be enabled on the database.
 - 3760 – Cannot drop index ‘%.*ls’ on view ‘%.*ls’ that has SNAPSHOT_MATERIALIZATION.
 - 4524 – Cannot alter view ‘%.*ls’ because it has snapshot materialization.
 - 4525 – Cannot use hint ‘%ls’ on view ‘%.*ls’ that has snapshot materialization before the view is refreshed.

And new Extended Events:

[![Snapshot view Extended Events][1]][1]

So how can we create a snapshot-materialized view? (Microsoft hasn't documented it yet, obviously.) Here's a [gist with things I've tried so far][2] that haven't worked.

  [1]: https://i.stack.imgur.com/qxTlM.png
  [2]: https://gist.github.com/BrentOzar/fa611ac88bda7151536a5ac5043baa34
Top Answer
Paul White (imported from SE)
### You can't. The feature is disabled in 2017 RTM.


That said, you can...

Using AdventureWorks:

    SELECT P.ProductID, COUNT_BIG(*) AS cbs
    FROM Production.Product AS P
    JOIN Production.TransactionHistory AS TH
        ON TH.ProductID = P.ProductID
    GROUP BY P.ProductID;

Changes to the underlying tables are not immediately reflected in the view (as is normally the case with SQL Server). Likewise, data modifications against the underlying tables do not have to maintain the snapshot indexed view.

To refresh the view contents, one needs to call one of the new stored procedures:

    EXECUTE sys.sp_refresh_single_snapshot_view
        @view_name = N'dbo.TH',
        @rgCode = 0; -- don't know what this is for yet

This produces the execution plan:


This likely won't work for you, because either an undocumented trace flag is needed, or you need to do the particularly nasty thing I did: writing to the memory location holding the feature flag (using a debugger) to enable this feature.

If you're curious, the feature flag is the byte at `sqllang!g_featureSwitchesLangSvc+0x10f`. It is checked during `sqllang!SpRefreshSingleSnapshotView`.

If you want to play along, and are fully prepared to accept the consequences of hacking about in SQL Server's code while it is running, and using a feature that Microsoft does not think is ready yet:

1. Attach a debugger to the SQL Server 2017 process. I use WinDbg.
2. Set a breakpoint:

        bp sqllang!SpRefreshSingleSnapshotView

3. Resume SQL Server using the Go command (`g`)
4. Create the view above, but not the unique clustered index yet
5. Run the `sys.sp_refresh_single_snapshot_view` command above
6. When the breakpoint is hit, step through until you see the code line:

        cmp byte ptr [sqllang!g_featureSwitchesLangSvc+0x10f (00007fff`328dfbcf)],0

 The offset may be different in other builds, for example in 2017 RTM CU3 it is `sqllang!g_featureSwitchesLangSvc+0x114`

7. The memory address inside the parentheses may be different. Use the one you see.
8. Use the display memory command to see the current value at the memory address you found:

        db 00007fff`328dfbcf L1

9. This should show a zero, indicating that the feature is disabled.
10. Change the zero to a one, using the enter values command (again with your memory address):

        eb 00007fff`328dfbcf 1

11. Disable the breakpoint and resume running SQL Server.
12. The feature is now enabled.
13. Build the unique clustered index on the view.
14. Play around.


Note `SNAPSHOT_MATERIALIZATION` allows us to materialize a snapshot of a query specification that ordinarily could not be indexed, for example the below uses `MAX`:

    SELECT TH.ProductID, MaxTransactionID = MAX(TH.TransactionID)
    FROM Production.TransactionHistory AS TH
    GROUP BY TH.ProductID;


Commands completed successfully.

  [1]: https://i.stack.imgur.com/RX266.png

This room is for discussion about this question.

Once logged in you can direct comments to any contributor here.

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.