How can I get partition elimination with a variable? I have four identical tables on four servers that I need to query from a fifth.  The fifth server is linked to the other four and has a view created to unify them.

-- Servers 1-4
CREATE TABLE Transactions
(
    TxID int not null primary key,
    Description nvarchar(50) not null
);

-- Server 5
CREATE VIEW vwTransactions
AS
SELECT 1 as ServerID, * FROM Server1.DB.dbo.Transactions
UNION ALL
SELECT 2 as ServerID, * FROM Server2.DB.dbo.Transactions
UNION ALL
SELECT 3 as ServerID, * FROM Server3.DB.dbo.Transactions
UNION ALL
SELECT 4 as ServerID, * FROM Server4.DB.dbo.Transactions;


When I run a query with a constant where clause on the ServerID column, I get a nice happy query plan that eliminates three of the partitions.

SELECT * FROM vwTransactions WHERE ServerID = 1;




But when I use a parameter, or worse &ndash; a join, I get a scan of all four tables.

DECLARE @ServerID int = 1;

SELECT *
FROM vwTransactions
WHERE ServerID = @ServerID;




I have also tried using inline table valued functions to no avail.

How do I make a view of these tables which can be queried easily?
In this case, adding an OPTION(RECOMPILE) hint to the query will get you the plan you want.

DECLARE @ServerID int = 1;

SELECT *
FROM vwTransactions
WHERE ServerID = @ServerID
OPTION (RECOMPILE);


The hint allows SQL Server to compile a plan specific to the current value of @ServerID. More specifically, it enables the Parameter Embedding Optimization, meaning the query is compiled as if you had written WHERE ServerID = 1, allowing partition elimination.