ALTER TABLE SWITCH fails with indexed view

I'm having issues with ALTER TABLE SWITCH between a staging table and a target table which is schema-bound to an indexed view.

When I issue the SWITCH statement, e.g

ALTER TABLE dbo.MASTERPrices_Staging switch TO dbo.MASTERPrices;

I get the following message:-


Msg 11402, Level 16, State 1, Line 1
ALTER TABLE SWITCH statement failed. Target table 'MASTERPrices' is referenced by 1 indexed view(s), but source table 'MASTERPrices_Staging' is only referenced by 0 indexed view(s). Every indexed view on the target table must have at least one matching indexed view on the source table.

We use only SQL Server 2008 Standard Edition so partitioning is not a solution. I need to SWITCH about 10 million rows daily, yet not loose the indexed view solution?

If I alter the view to not schema bound, then SWITCH Works, but when I ALTER the view again and set it back to SCHEMABOUND, all the indexes (12 of them including clustered index have dissapeared)

Any ideas anybody?

You need an indexed view on the staging table that matches the definition of the one on the production table, and indexes on the staging view that match every index on the production view.

See SqlFiddle

The idea is that the engine must replace every index, including the ones declared on the views. If it has build an index (ie. if there is an index on production but not on staging) then the switch will fail. Also all constraints, filters etc must match so that the engine knows that the data is valid (staging data will not violate production constraints).

Not sure why you need outer joins or anything similar, this should be straight forward.

StackExchange Database Administrators Q#56433, answer score: 5