0

Are materialized views available on sql server 2012 or latest? I'am trying to create materialized view like this:

CREATE MATERIALIZED VIEW [NAME] AS...

but I have exception:

Unknown object type 'MATERIALIZED' used in a CREATE, DROP, or ALTER statement.

For now I'am using standard view (create view...), but is generated each time the view is used. In my script I have left join's and a lot of outer apply between two databeses so I can't create indexed view... So i want to create materialized view because it defines a view that is persisted in durable storage and incrementally updated as new data arrives.I wanted to check if such a view would be faster.

Or is there any other solution that will allow me to create a table that will be fed with data from several other tables on an ongoing basis? Need to support 'outer apply', ideally the data should be stored somewhere and only updated if something changes, rather than the script underneath executing every time the data is read.

blane
  • 159
  • 11
  • `a lot of outer apply between two databeses` how do you expect to have a materialized view in that case? A materialized view returns the actual data, not a stale copy. The data in a materialized view is always updated when the source data changes. You can't have a materialized view if the database engine can't determine the state of the rows. You can create a cached copy of the data, possibly using Change Tracking, or heavier solutions like replication – Panagiotis Kanavos Mar 15 '23 at 11:16
  • What's the actual problem you want to solve? Caching the remote database's data? – Panagiotis Kanavos Mar 15 '23 at 11:21
  • That's why I found such a solution as a materialized view (not to be confused with an ordinary view in sql server), but as you can see it is only available on azure. – blane Mar 15 '23 at 11:24
  • The standard view I have now without indexes is quite slow :( – blane Mar 15 '23 at 11:25
  • @blane I'm sorry the question was wrongly closed as a duplicate before I saw it. Have you considered putting trigger(s) on the source table(s) that would cause your cached "view" to update as needed? – Emperor Eto Mar 15 '23 at 11:30
  • That's a different problem with several solutions already. You're using `materialized view` in the narrow sense of a cache of remote data. One solution is to use [replication](https://learn.microsoft.com/en-us/sql/relational-databases/replication/types-of-replication?view=sql-server-ver16) between the servers/databases. Depending on how frequently the data changes and up-to-date you want the data to be, you can use transactional replication or snapshot replication on a schedule. That's available out of the box since the 1990s – Panagiotis Kanavos Mar 15 '23 at 11:30
  • Perhaps if you ask a new question entitled "Alternatives to Materialized Views in SQL Server?" someone will be able to give more useful answer(s). – Emperor Eto Mar 15 '23 at 11:31
  • 1
    The question isn't about materialized views, at least not in the sense used in SQL Server for the last 30 years. It's about caching remote data. That's available through other technologies. Azure Synapse itself doesn't create a local copy of remote data. They're `virtual tables created with SELECT expressions and presented to queries as logical tables` virtual tables created with SELECT expressions and presented to queries as logical tables. That's what closer to what Polybase does – Panagiotis Kanavos Mar 15 '23 at 11:33
  • `CREATE MATERIALIZED VIEW ... AS` [only applies to Azure Synapse Analytics](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-materialized-view-as-select-transact-sql?view=azure-sqldw-latest&preserve-view=true) - it looks like they dabbled with another `SNAPSHOT_MATERIALIZATION` feature but that never came to fruition https://dba.stackexchange.com/a/187536/3690 – Martin Smith Mar 15 '23 at 12:40
  • In addition to replication, it is also possible to create ETL processes directly from change data capture logs as well, though this can get complex. Depending on how much control you have over how data gets to the tables to begin with, you could consider modifying the processes such as stored procedures or data classes to duplicate the data for you. Depending on how much control you have over the data architecture, you could reevaluate why there are separate databases. And if the data updates in practice were only new rows, this could simplify your needs. – Sean Mar 15 '23 at 13:57
  • If the need for queries is reporting, you could also look into adding a reporting specific product that is designed to aggregate data from multiple sources, such as PowerBI or Tableau. They can pull data from two databases, save into a reporting model, and then query that. – Sean Mar 15 '23 at 13:59
  • @PanagiotisKanavos it is possible to use replication in the same database to create a new table which contains data from the same database (few tables) and another database? refresh data must mu immediatelly. – blane Jun 30 '23 at 10:39
  • What problem is this supposed to solve? You already have the live data. Why do you want to make a copy of them? Are you trying to create a report over live data and try to avoid blocking? That's a bad idea for a lot of reasons, not just the inevitable blocking. The shape of transactional data is different from the shape needed for reporting. – Panagiotis Kanavos Jun 30 '23 at 10:45
  • I want to use Odata and connect all new table to grind on front-end. I need to show in front-end grid data from few tables from two databeses and i need to have sorting, filtering, pagging. – blane Jul 10 '23 at 12:38
  • Isn't there any way in sql server to store a View locally as a 'copy' or something working like other DB tables? To fast access, and in case of changes in related tables to be updated in background? – blane Jul 28 '23 at 13:21

0 Answers0