x

Alter table switch

I am getting the error Msg 11404, Level 16, State 1, Line 2

ALTER TABLE SWITCH statement failed. Target table XXXX
is referenced by 10 indexed view(s), but source table XXX'
is only referenced by 5 matching indexed view(s).
Every indexed view on the target table must have at least one matching indexed view on the source table. when trying to switch destination table to source table.

I have checked that all indexes are the same in both source and destination tables plus dependencies and also in the same file group. Also checked the constraints and they are all fine. What are the other possible issues that I should be on the look out for?

Thanks again!
C!
more ▼

asked Jan 25, 2012 at 06:17 AM in Default

Wang gravatar image

Wang
11 2 2 3

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first
By the looks of things you have multiple indexed views. You'll need to match those up in order to be able to perform the switch (it's not just the indexes on the table that have to line up)
more ▼

answered Jan 25, 2012 at 07:34 AM

SirSQL gravatar image

SirSQL
4.8k 1 3

One example of the indexes created for the views is as follows:

CREATE UNIQUE CLUSTERED INDEX [PK_FCT_TRANSFH_TBL] ON [staging].[FCT_TRANSFH_TBL] ([YEAR], [REPORTER], [HS], [SRC_01_FLAG]) ON CWDC_PG{@V_YEAR}

When I comment out where the index is to be aligned on the filegroup i.e. --ON CWDC_PG{@V_YEAR}, I get no error on the alter table switch. I thought that part of aligning the indexes was ensuring its aligned to the partition of the table its a dependent on, right? If there is nothing wrong with my syntax then what are the other possible issues?
Jan 28, 2012 at 02:31 PM Wang
(comments are locked)
10|1200 characters needed characters left
I have checked the views too and created the indexes for them on the partition. Do functions and stored procedures affect the switch too?
more ▼

answered Jan 25, 2012 at 07:47 AM

Wang gravatar image

Wang
11 2 2 3

No, functions and procedures don't matter. You just have to ensure that the schema matches up.
Jan 25, 2012 at 08:07 AM SirSQL
Please explain further on multiple indexed views. How about the statistics? Is there a statement to run to ensure the statistics are copied or not to be copied?
Jan 25, 2012 at 12:29 PM Wang
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x53
x41

asked: Jan 25, 2012 at 06:17 AM

Seen: 1558 times

Last Updated: Jan 25, 2012 at 07:26 AM