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:


Any memory refresher?

  • 4
    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.
    – Dan Guzman
    Commented Jun 29, 2020 at 11:08
  • 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? Commented Jun 29, 2020 at 12:42
  • 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. Commented Jun 29, 2020 at 12:51
  • 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? Commented Jun 29, 2020 at 13:04
  • 2
    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. Commented Jun 29, 2020 at 13:10

2 Answers 2


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:

      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


    DELETE FROM SnapshotOfSourceTable
        SELECT C1 
        FROM SourceTable

    UPDATE Snap
          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
        SELECT C1 
        FROM SnapshotOfSourceTable

    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

        @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;

  • 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. Commented Jun 29, 2020 at 13:48

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.