Testing Snapshot Materialized Views.sql

/*
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.
*/

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