CODEWITHSUNDEEP
t-sqlazure-sql-database
YuseqYaseq
YuseqYaseq

Reputation: 283

How to create materialized view of an external table

CREATE VIEW materialized_view WITH SCHEMABINDING AS
SELECT ...
FROM ext.external_table

Fails with

The option 'SCHEMABINDING' is not supported with external tables.

If I understand correctly SCHEMABINDING is necessary to make a materialized view. How can I correct this query?

Upvotes: 0

Views: 1790

Answers (1)

Razvan Socol
Razvan Socol

Reputation: 5684

You cannot create an indexed view based on tables that are in a different database.

I think your options are:

a) create the indexed view in the other database and create a regular view in this database to query that indexed view

b) create a copy of the table in this database and a mechanism to update this table whenever the data is changed in the table which is in the other database; this could be done with triggers, replication, a stored procedure called on a schedule, etc.

Upvotes: 1

Related Questions