ALTER TABLE SWITCH fails with indexed view

Question

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?

Answer 1

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.

Answer 2

You need to define the same indexed view on the staging table before you can switch, since the indexed view has it's own partition. To explain the reason for this, first imagine the following simple schema:

-- TWO SIMPLE TABLES TO SWITCH
CREATE TABLE dbo.T1 (ID INT NOT NULL CONSTRAINT PK_T1_ID PRIMARY KEY, Filler CHAR(1000) NULL);
CREATE TABLE dbo.T2 (ID INT NOT NULL CONSTRAINT PK_T2_ID PRIMARY KEY, Filler CHAR(1000) NULL);
GO 

-- CREATE INDEXED VIEW ON FIRST TABLE
CREATE VIEW dbo.V1 
WITH SCHEMABINDING
AS
    SELECT  ID, Filler
    FROM    dbo.T1;
GO
CREATE UNIQUE CLUSTERED INDEX IX_V1_ID ON dbo.V1 (ID);
GO

-- CREATE INDEXED VIEW ON SECOND TABLE
CREATE VIEW dbo.V2
WITH SCHEMABINDING
AS
    SELECT  ID, Filler
    FROM    dbo.T2;
GO
    CREATE UNIQUE CLUSTERED INDEX IX_V2_ID ON dbo.V2 (ID);
GO

-- CREATE NON INDEXED VIEW ON FIRST TABLE
CREATE VIEW dbo.V3
WITH SCHEMABINDING
AS
    SELECT  ID, Filler
    FROM    dbo.T1;
GO

-- ADD SOME DATA TO SWITCH
INSERT dbo.T2 (ID) VALUES (1);

If I run this:

SELECT  p.partition_id, o.object_id, o.Name, p.rows
FROM    sys.partitions p
        INNER JOIN sys.objects o
            ON o.object_id = p.object_id
WHERE   o.Name IN ('T1', 'T2', 'V1', 'V2', 'V3');

I get the following result:

partition_id        object_id   Name    rows
72057594886553600   1948794250  T1      0
72057594886619136   1980794364  T2      1
72057594886684672   2012794478  V1      0
72057594886750208   2028794535  V2      1

Because the view V3 has no index defined on it, it has no entry in sys.partitions. If I then run:

ALTER TABLE dbo.T2 SWITCH TO dbo.T1; 

and query the partitions again I get the following result

partition_id        object_id   Name    rows
72057594886553600   1980794364  T2      0
72057594886619136   1948794250  T1      1
72057594886684672   2028794535  V2      0
72057594886750208   2012794478  V1      1

You can see all that has happened is that the partitions have just switched between the corresponding tables/views. If you do not have the indexed view on the second table you would only get the following partitions:

partition_id        object_id   Name    rows
72057594886553600   1948794250  T1      0
72057594886619136   1980794364  T2      1
72057594886684672   2012794478  V1      0

And the partition on V1 would have nothing to "switch" to.

Example on SQL Fiddle

Answer 3

Well it would appear that it's not possible to SWITCH between two identical tables, one a staging table and the other the target when they both have an indexed view, using simply SQL Server 2008 standard edition!

I have tried successfully:-

1) Switching data between both tables without any associated schemabound view. 2) Switching data between both tables, each with an associated schemabound view, but no PK.

I cannot Switch the data from staging to target once a primary key has been added to each of the two views, where view 1 and view 2 are identical, except that view 1 uses the target table joined to other tables and view 2 uses the staging table joined to the same 'other' tables as view 1, and also the columns selected in both of the views' PKs are the same. Also note that the underlying tables including nullability are also identical as are constraints - crazy