CODEWITHSUNDEEP
sqlsql-serverazureazure-sql-database
Rohith PH
Rohith PH

Reputation: 3

Materialized view in Marketing Performance DB in Azure SQL database

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?

Upvotes: 0

Views: 436

Answers (1)

SQLpro
SQLpro

Reputation: 5103

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"

Upvotes: 0

Related Questions