Odd execution plan when constraint is added to partition view

Question

I have a weird issue with a partitioned view I setup.

For example sake, lets say I have an Orders & Orders_Archive table which is accessed via a partitioned view. Each order has an OrderType, which is accessed via a foreign key and stored in a look table.

Orders have a field to denote whether or not it is active or archived: [ArchiveYearMonth] char(5). (I realize storing a date in a string is not optimal, but ignore that for the sake of the example).

The schema for the three tables look like this:

CREATE TABLE Orders
(
    Order_ID uniqueidentifier  DEFAULT NEWSEQUENTIALID(),
    OrderType_ID int        NOT NULL,
    ArchiveYearMonth AS ('9999/12') PERSISTED,
    CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED  (Order_ID )
)
CREATE TABLE Orders_Archive
(
    Order_ID uniqueidentifier  DEFAULT NEWSEQUENTIALID(),
    OrderType_ID int       NOT NULL,
    CompletedOn datetime2(3) NOT NULL,
    ArchiveYearMonth AS (dbo.[ConvertToYearMonth](CompletedOn)) PERSISTED,
    CONSTRAINT [PK_Orders_Archive] PRIMARY KEY CLUSTERED  (Order_ID )
)
CREATE TABLE OrderTypes
(
    OrderType_ID int IDENTITY(1,1),
    OrderType varchar(100),
    CONSTRAINT [PK_OrderTypes] PRIMARY KEY CLUSTERED  (OrderType_ID )
)
GO

I have setup a view like this:

CREATE VIEW AllOrders_ProperFilter
AS
    SELECT Order_ID, OrderType, ArchiveYearMonth
    FROM Orders o INNER JOIN OrderTypes oT on o.OrderType_ID = oT.OrderType_ID
    WHERE ArchiveYearMonth = '9999/12'
    UNION ALL
    SELECT  Order_ID, OrderType, ArchiveYearMonth
    FROM Orders_Archive o INNER JOIN OrderTypes oT on o.OrderType_ID = oT.OrderType_ID
    WHERE ArchiveYearMonth <>'9999/12'
GO

Which works as expected when querying directly against it:

SELECT * FROM AllOrders_ProperFilter WHERE ArchiveYearMonth = '9999/12'

Actual execution plan results in only Orders & OrderTypes being scanned:

Table 'OrderTypes'. Scan count 1, logical reads 52, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Orders'. Scan count 1, logical reads 102, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

However, when I join the partitioned view against another table I get some unexpected behavior in the execution plan.

Setup a small table with some record from both Orders & Order_Archive tables:

SELECT * INTO #tempTbl
FROM
(
    SELECT TOP (10) Order_ID
    FROM Orders
    UNION ALL 
    SELECT TOP (10) Order_ID
    FROM Orders_Archive
) z

Now join the original view against the temporary table:

SELECT * 
FROM AllOrders_ProperFilter pView
WHERE Order_ID IN (SELECT Order_ID FROM #tempTbl) AND ArchiveYearMonth  = '9999/12'
option (recompile)

The results are okay, and only the Orders table is queried against, but the execution plan is terrible. Both the Orders & OrderTypes tables are scanned, joined and then joined against the temporary table. (Orders table has 20,000 records; the temporary table has 20).

Execution plan against AllOrders_ProperFilter

Now if I remove the partitioning from the view, and query against it I get a different result:

CREATE VIEW AllOrders
AS
    SELECT Order_ID, OrderType, ArchiveYearMonth
    FROM Orders o INNER JOIN OrderTypes oT on o.OrderType_ID = oT.OrderType_ID
    WHERE ArchiveYearMonth = '9999/12'
    UNION ALL
    SELECT  Order_ID, OrderType, ArchiveYearMonth
    FROM Orders_Archive o INNER JOIN OrderTypes oT on o.OrderType_ID = oT.OrderType_ID
 --   WHERE ArchiveYearMonth <>'9999/12'
GO

The execution plan now scans the temporary table, uses the PK to seek against the Orders table, and then joins against OrderTypes. Expectantly, It also queries against the Orders_Archive table, since the constraint is removed.

Execution plan against AllOrders

Why does the view with both constraints ([AllOrders_ProperFilter]) perform so poorly when another table is joined against it?

Note - I get the same results when I add actual constraints against the tables:

ALTER TABLE Orders ADD CONSTRAINT
    CK_NOTARCHIVED CHECK (ArchiveYearMonth = '9999/12')
ALTER TABLE Orders_Archive ADD CONSTRAINT
    CK_ARCHIVED CHECK (ArchiveYearMonth <> '9999/12')

Apparently I can't post more than two links, but I have a sample script which re-creates the issue with dummy data.