Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
/*
This message gives me a clue about the database requirements:
10650 – Refresh of snapshot view(s) requires snapshot isolation to be enabled on the database.
So let's set that up:
*/
CREATE VIEW dbo.Ocean WITH SNAPSHOT
AS
SELECT Id, DisplayName FROM dbo.Users;
GO
/* Fails:
Msg 195, Level 15, State 1, Procedure Ocean, Line 1 [Batch Start Line 0]
'SNAPSHOT' is not a recognized option.
*/
CREATE VIEW dbo.Ocean WITH SNAPSHOT_MATERIALIZATION
AS
SELECT Id, DisplayName FROM dbo.Users;
GO
/* Fails:
Msg 195, Level 15, State 1, Procedure Ocean, Line 1 [Batch Start Line 9]
'SNAPSHOT_MATERIALIZATION' is not a recognized option.
*/
CREATE VIEW dbo.Ocean WITH MATERIALIZATION_SNAPSHOT
AS
SELECT Id, DisplayName FROM dbo.Users;
GO
/* Fails:
Msg 195, Level 15, State 1, Procedure Ocean, Line 1 [Batch Start Line 18]
'MATERIALIZATION_SNAPSHOT' is not a recognized option.
*/
/* Screw it, let's try the hint on the index instead */
CREATE VIEW dbo.Ocean WITH SCHEMABINDING
AS
SELECT Id, DisplayName FROM dbo.Users;
GO
/* Succeeds */
CREATE UNIQUE CLUSTERED INDEX IX_Id ON dbo.Ocean(Id) WITH (SNAPSHOT = MATERIALIZED);
/*
Fails:
Msg 155, Level 15, State 1, Line 38
'SNAPSHOT' is not a recognized CREATE INDEX option.
Msg 102, Level 15, State 1, Line 38
Incorrect syntax near 'MATERIALIZED'.
*/
CREATE UNIQUE CLUSTERED INDEX IX_Id ON dbo.Ocean(Id) WITH (SNAPSHOT = ON);
/*
Fails:
Msg 155, Level 15, State 1, Line 49
'SNAPSHOT' is not a recognized CREATE INDEX option.
*/
CREATE UNIQUE CLUSTERED INDEX IX_Id ON dbo.Ocean(Id) WITH (SNAPSHOT_MATERIALIZED = ON);
/*
Nope:
Msg 155, Level 15, State 1, Line 57
'SNAPSHOT_MATERIALIZED' is not a recognized CREATE INDEX option.
*/
CREATE UNIQUE CLUSTERED INDEX IX_Id ON dbo.Ocean(Id) WITH (MATERIALIZED_SNAPSHOT = ON);
/*
No soup for you:
Msg 155, Level 15, State 1, Line 65
'MATERIALIZED_SNAPSHOT' is not a recognized CREATE INDEX option.
*/
/* What if it goes in front of the index? */
CREATE SNAPSHOT INDEX IX_Id ON dbo.Ocean(Id);
/*
Fails:
Msg 343, Level 15, State 1, Line 74
Unknown object type 'SNAPSHOT' used in a CREATE, DROP, or ALTER statement.
*/
CREATE SNAPSHOT_MATERIALIZED INDEX IX_Id ON dbo.Ocean(Id);
/*
Like Brent in math class:
Msg 343, Level 15, State 1, Line 83
Unknown object type 'SNAPSHOT_MATERIALIZED' used in a CREATE, DROP, or ALTER statement.
*/
CREATE MATERIALIZED_SNAPSHOT INDEX IX_Id ON dbo.Ocean(Id);
/*
Nopetopus:
Msg 343, Level 15, State 1, Line 91
Unknown object type 'MATERIALIZED_SNAPSHOT' used in a CREATE, DROP, or ALTER statement.
*/
@alexbransky

This comment has been minimized.

Copy link

@alexbransky alexbransky commented Oct 4, 2017

What is snapshot materialization supposed to do, once it's actually released?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment