3

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?

migrated from stackoverflow.com Jan 9 '14 at 10:17

This question came from our site for professional and enthusiast programmers.

  • Thanks Remus. I already considered this and attempted to left outer join the staging table to the target table in the indexed view - however indexed views don't support outer joins! – Ian Currie Jan 9 '14 at 10:25
4

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.

  • OK, I have created a new indexed view on the staging table (see comments above) - both indexed views only have 1 primary key to keep things simple. But still getting error ? Should the staging table indexed view also contain the target table too ? – Ian Currie Jan 9 '14 at 11:30
  • I simplified the views - both staging and target with same PK. but now it can error alternately with 'target must be empty' and 'the old error !!!! – Ian Currie Jan 9 '14 at 15:00
4

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

  • Hi Gareth. The definition of the View V3 is defined as SCHEMABOUND ? yet you described it as non-indexed ? Also, as I'm running Standard edition 2008, I can't do partitioning ? – Ian Currie Jan 9 '14 at 10:35
  • Yes, schemabound and indexed are not the same thing. A view needs to be schemabound to be indexed, but it does not necessarily have an index if it is schemabound. With regard to partitioning even though you may not be able to split tables into multiple partitions, it does not mean tables/views do not have underlying partitions, it just means each table/view can only have a single partition. – GarethD Jan 9 '14 at 10:39
  • OK - I think I'm starting to get this. I was confused with SCHEMABOUND. I'm restoring an DB at the moment and bear this in mind to test. Is it OK to get back to you on this ? – Ian Currie Jan 9 '14 at 10:42
  • If a view is schema bound it prevents changes to the objects within it, (example here). When it is not schemabound you can make changes that will potentially break the view, but the error will only be raised when the view is used (example here) – GarethD Jan 9 '14 at 10:49
  • So Gareth, If I understand this correctly, in my example, If I add a new indexed view, (but with a different name) associated with my staging table that references the same tables as the indexed view in the target table, that would remedy the error ? – Ian Currie Jan 9 '14 at 10:53
2

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

  • This is not just a limitation of SQL Server 2008 standard edition. It will not work in SQL Server 2012 either if your view references other tables. – GarethD Jan 9 '14 at 17:07
  • That is a hell of a limitation ! It's generally the idea that a view allows you to join a couple of tables ! Thankyou very much Gareth for your efforts researching and helping me – Ian Currie Jan 10 '14 at 9:11
  • So why does your example not work for me even though your views (V1; V2 and V3) are all based on one table ? – Ian Currie Jan 10 '14 at 9:26
  • I really don't know, I have run it successfully on 2008 (SP3) Standard Edition, 2008 R2 (Express and Standard) and 2012 Express Edition. I can't seen anything in any of the 2008 service packs that would indicate this wouldn't work without them (unfortunately I only have 3 different versions to test on though). – GarethD Jan 10 '14 at 9:56
  • Maybe my permissions. We are using 2008 (SP3) Standard !!! – Ian Currie Jan 10 '14 at 11:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.