0

I want to create a materialized view in a marketing performance database but am getting an error while trying to create .

Using below query to create

CREATE MATERIALIZED VIEW InvoicesTempM  
WITH (distribution = hash(Invoiceid), FOR_APPEND)  
AS
SELECT Invoiceid, COMPANY from dbo.Invoices

Getting error as below:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'CREATE MATERIALIZED VIEW'.
Completion time: 2023-02-09T10:23:12.8418869+05:30

Could anyone help here how can we create materialized view in Azure SQL database, if we cannot please share if there is any alternatives?

Dale K
  • 25,246
  • 15
  • 42
  • 71
Rohith PH
  • 3
  • 1
  • 1
    If you read the documentation it appears to me that a materialised view only applies to Azure Synapse. However this might answer your question https://stackoverflow.com/questions/3986366/how-to-create-materialized-views-in-sql-server – Dale K Feb 09 '23 at 05:24

1 Answers1

0

MATERIALIZED VIEW are a concept invented by Oracle. The closest topic in Microsoft SQL Server are INDEXED VIEW, that is :

  • a classical view with some limits and the SCHEMABINDING option
  • a UNIQUE CLUSTERED index that is created on the view.

In MS SQL Server all Indexed Views are alwyas synchronized with source data (do not need to refresh).

Refer to "Create indexed views"

SQLpro
  • 3,994
  • 1
  • 6
  • 14