Sql-server – Materialized view in SQL Server

materialized-viewsql serversql-server-2005

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?

Best Answer

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
Related Question