0

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?

  • 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 Jun 29 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? – Andrea Moro Jun 29 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. – Tibor Karaszi Jun 29 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? – Andrea Moro Jun 29 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. – Tibor Karaszi Jun 29 at 13:10
1

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.

| improve this 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
| improve this answer | |
New contributor
Collin is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
  • 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. – Andrea Moro Jun 29 at 13:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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