Materialized view in SQL Server

Question

Have a bit of glitches from the time I was using SQL Server back in 2006 and the many other databases I played with (and which I have not touched since then).

I need to create a materialized view to quickly get access to the data without overloading the DB but also to pull them what it was made static by the time the view was created.

SQL Server seems to support the materialized views with the Indexed views, but to be created these require among the other thing the following:

• the WITH SCHEMABINDING construct
• a clustered INDEX

Failing in that, my understanding is the view is not auto refreshing, which is probably what I need?

My goal is to get a view, that I can refresh whenever I need. So far I created a Materialized view in the SQL 2005 instance I have access to with the following statement:

CREATE MATERIALIZED VIEW xys AS
SELECT 123
FROM X
WHERE ...
GROUP BY

Any memory refresher?

• Dan Guzman: CREATE MATERIALIZED VIEW is invalid syntax in all versions of SQL Server so I an puzzled as to how you created the view with the DDL in your question. SQL Server indexed views are not static so you need a different approach, such as a table that is refreshed periodically or a view referencing a database snapshot.
• Andrea Moro: Actually it is a good question @DanGuzman. I gave the DDL to the person who has access who later confirmed it was a 2005 version. But eventually it did manage to create it, though my user has no grants to see the structure anymore nor to query the all_views table (assuming it still exists). So not sure the DDL is changed or not. I assume it is not, which mean some kind of alias may exist? The Materialised view construct seems to be support in Azure, but this is not the case. Ideas?
• Tibor Karaszi: The CREATE MATERIALIZED VIEW command is in Azure Synapse Analytics (SQL DW), but not in other SQL servers. There are no such alias functionality, so the only wan such a command could be accepted would be a database gateway, which changes the command to something else before submitting it to SQL Server. Pretty far-fetched. I bet a beer that it was an indexed view they are using. Or a database snapshot. And then some confusion along the way.
• Andrea Moro: So it may be a traditional View @Tibor Karaszi? It may be not that bad I guess. Though the data on the view is refreshed all the time as far as I remember, right?
• Tibor Karaszi: A traditional view is like a macro, and with an index you can "materialize" the data for a view. But when you query a view, you always see "fresh data", regardless of type of view. I.e., an index on a view is maintained in real-time, when the modification is performed. Sp_refreshview is about meta-data. Say you have SELECT * in a view and add a column to the data - that won't be visible though the view until sp_refreshview.

Answer 1

Thinking about the requirements:

• "quickly get access to the data without overloading the DB"
• "pull them what was made static [at] the time the view was created"

Especially the part about the data being static captured at a moment in time, I doubt a view is what you want. I think you may want something more like CDC, but that wasn't introduced until SQL Server 2008. For SQL Server 2005, a database snapshot may meet your needs.

Dominique's answer also potentially an option. There are multiple ways to fulfil above two requirements. Another example might be to create a stored procedure as shown below and schedule via SQL Server Agent. E.g. assuming source table:

CREATE TABLE SourceTable
(
 C1 [datatype1] PRIMARY KEY 
 , C2 [datatype2]
 , C3 [datatype3]
)

and a "Snapshot table" created to match:

CREATE TABLE SnapshotOfSourceTable
(
 C1 [datatype1] PRIMARY KEY
 , C2 [datatype2]
 , C3 [datatype3]
)

then you could do something like this:

CREATE PROCEDURE dbo.UpdateSnapshotOfSourceTable
AS
BEGIN
BEGIN TRAN
BEGIN TRY
 DELETE FROM SnapshotOfSourceTable
 WHERE C1 NOT IN
 (
 SELECT C1 
 FROM SourceTable
 )
 UPDATE Snap
 SET 
 Snap.C2 = Source.C2
 , Snap.C3 = Source.C3
 FROM SnapshotOfSourceTable [Snap]
 INNER JOIN SourceTable [Source]
 ON Snap.C1 = Source.C1
 INSERT INTO SnapshotOfSourceTable
 (C1, C2, C3)
 SELECT C1, C2, C3
 FROM SourceTable
 WHERE C1 NOT IN
 (
 SELECT C1 
 FROM SnapshotOfSourceTable
 )
 COMMIT
END TRY
BEGIN CATCH
 DECLARE @ErrorMessage NVARCHAR(4000);
 DECLARE @ErrorSeverity INT;
 DECLARE @ErrorState INT;
 SELECT 
 @ErrorMessage = 'Error when attempting to update snapshot table from source: ' + ERROR_MESSAGE(),
 @ErrorSeverity = ERROR_SEVERITY(),
 @ErrorState = ERROR_STATE();
 RAISERROR (@ErrorMessage, -- Message text.
 @ErrorSeverity, -- Severity.
 @ErrorState -- State.
 )
 WITH LOG;
 ROLLBACK
END CATCH
END

• Andrea Moro: So view in the old version of SQL server are constantly updated all the time I access to it. Is that what I got so far then. The only advantage is that I filtered out lots of noise, but I may end up the DB playing balls with the huge amount of queries I have to run.

Answer 2

For that kind of need, I like to build a SSIS package that will copy the content of the view to a different SQL DB (on a different instance if I want to make sure their reporting won't hurt the prod DB).

You can then schedule the data refresh when you want and you can also create index that would make their reports run faster.