Sql-server – Indexed View over multiple partitioned tables and ALTER TABLE SWITCH

materialized-viewpartitioningsql serversql-server-2012

Is it possible to have an indexed view that combines 2 partitioned tables and still use ALTER TABLE SWITCH to move data out of both base tables? I'm receiving the following error when trying this and can't seem to figure out how to solve it (if it's even solvable):

ALTER TABLE SWITCH statement failed. Table 'im_db.dbo.SM_MEDIA_NEUTRAL_STATE_FACT' is not aligned with the index 'IX_V_STATE_RSN_AGG_BASE' on indexed view 'V_STATE_RSN_AGG_BASE'. The table is partitioned on column 'START_DATE_TIME_KEY', but the index on the indexed view is partitioned on column 'start_date_time_key', which is selected from a different column 'START_DATE_TIME_KEY' in table 'SM_RES_STATE_REASON_FACT'. Change the indexed view definition so that the partitioning column is the same as the table's partitioning column.;

I've tried changing the partition column but it doesn't matter which I use (from which base table).

Best Answer

It seems you are partitioning on one of the grouping columns of the indexed view aggregation. In this case, the indexed view will be aligned with only one of the partitioned tables, which is one with the exposed grouping column you're partitioning on.

The Partition Switching When Indexed Views Are Defined books online topic states:

Where the view references several tables (using joins, subqueries, functions, and so on), the indexed view is partition-aligned with only one of the partitioned tables.

Consequently, you'll be able to SWITCH partitions in/out of only one of the tables. Below is a demo script. 

CREATE PARTITION FUNCTION PF_START_DATE_TIME_KEY(int) 
    AS RANGE RIGHT FOR VALUES(1);
CREATE PARTITION SCHEME PS_START_DATE_TIME_KEY 
    AS PARTITION PF_START_DATE_TIME_KEY ALL TO ([PRIMARY]);
CREATE TABLE dbo.DATE_TIME_DIM(
      START_DATE_TIME_KEY int
    , DATE_TIME datetime2(0)
    );
CREATE TABLE dbo.SM_MEDIA_NEUTRAL_STATE_FACT(
      START_DATE_TIME_KEY int NOT NULL
    , CorrelationID int NOT NULL
    , Col1 int NOT NULL
    ) ON PS_START_DATE_TIME_KEY(START_DATE_TIME_KEY);
CREATE TABLE dbo.SM_RES_STATE_REASON_FACT(
      START_DATE_TIME_KEY int NOT NULL
      , CorrelationID int NOT NULL
      , Col1 int NOT NULL
    ) ON PS_START_DATE_TIME_KEY(START_DATE_TIME_KEY);
CREATE TABLE dbo.SM_MEDIA_NEUTRAL_STATE_FACT_STAGING(
      START_DATE_TIME_KEY int NOT NULL
    , CorrelationID int NOT NULL
    , Col1 int NOT NULL
    ) ON PS_START_DATE_TIME_KEY(START_DATE_TIME_KEY);
CREATE TABLE dbo.SM_RES_STATE_REASON_FACT_STAGING(
      START_DATE_TIME_KEY int NOT NULL
    , CorrelationID int NOT NULL
    , Col1 int NOT NULL
    ) ON PS_START_DATE_TIME_KEY(START_DATE_TIME_KEY);
GO

CREATE VIEW dbo.V_STATE_RSN_AGG_BASE
WITH SCHEMABINDING
AS
SELECT
      SM_RES_STATE_REASON_FACT.START_DATE_TIME_KEY
    , SUM(SM_MEDIA_NEUTRAL_STATE_FACT.col1) AS col1
    , SUM(SM_RES_STATE_REASON_FACT.col1) AS col2
    , COUNT_BIG(*) AS CountBig
FROM dbo.SM_MEDIA_NEUTRAL_STATE_FACT
JOIN dbo.SM_RES_STATE_REASON_FACT ON
    SM_MEDIA_NEUTRAL_STATE_FACT.CorrelationID = SM_RES_STATE_REASON_FACT.CorrelationID
GROUP BY SM_RES_STATE_REASON_FACT.START_DATE_TIME_KEY;
GO

CREATE UNIQUE CLUSTERED INDEX IX_V_STATE_RSN_AGG_BASE ON
    dbo.V_STATE_RSN_AGG_BASE(START_DATE_TIME_KEY)
    ON PS_START_DATE_TIME_KEY(START_DATE_TIME_KEY);
GO

--this SWITCH succeeds because the indexed view partitioning column is from the SM_RES_STATE_REASON_FACT table
TRUNCATE TABLE dbo.SM_RES_STATE_REASON_FACT_STAGING;
ALTER TABLE dbo.SM_RES_STATE_REASON_FACT
    SWITCH PARTITION $PARTITION.PF_START_DATE_TIME_KEY(1) 
    TO dbo.SM_RES_STATE_REASON_FACT_STAGING PARTITION $PARTITION.PF_START_DATE_TIME_KEY(1);
GO

--this SWITCH fails because the indexed view partitioning column is not from the SM_MEDIA_NEUTRAL_STATE_FACT table
TRUNCATE TABLE dbo.SM_MEDIA_NEUTRAL_STATE_FACT_STAGING;
ALTER TABLE dbo.SM_MEDIA_NEUTRAL_STATE_FACT
    SWITCH PARTITION $PARTITION.PF_START_DATE_TIME_KEY(1) 
    TO dbo.SM_MEDIA_NEUTRAL_STATE_FACT_STAGING PARTITION $PARTITION.PF_START_DATE_TIME_KEY(1);
GO
Related Question