Any SQL where clause performance similar to where 1=0 (i'm using SQL Server 2008R2 or later) 

For centralize manage and consistent, there is a view definition as follow:

CREATE VIEW view_all_situation
(
  select null as src, tf.col1, sum(col2) as fee, sum(isnull(v1.col1, 0)) as fee1 ...
  from table_fee tf
  inner join parameter_table pt on tf.id = pt.id
  left join view1 v1 on ...
  left join view2 v2 on ...
  where pt.col1 = 0
  group by tf.col1

  UNION ALL

  select tfd.src, tfd.col1, sum(col2) as fee, sum(isnull(v1.col1, 0)) as fee1 ...
  from table_fee_detail tfd
  inner join parameter_table pt on tfd.id = pt.id
  left join view1 v1 on ...
  left join view2 v2 on ...
  where pt.col1 = 1
  group by tfd.col1
)
(Note: parameter_table only have 1 row)


so if the value in parameter_table.col1 = 1 then return table_fee_detail else return table_fee. (Note: parameter_table only have 1 row)

As the first part require 10 secs and second part require another 10 secs, finally, it costs 20 secs to return. If there any faster way to let SQL Server execute either one part of the UNION ALL base on the parameter_table value so that it returns in 10 secs?

As dynamic SQL is not applicable in my situation, i can't build it to get the result.

I've tried to use 

where EXISTS (select 1 from parameter_table where col1 = 0) and 
where EXISTS (select 1 from parameter_table where col1 = 1) 


as the where clause, it cannot make the SQL Server to return in 10 secs. It still use 20 secs.

Is it possible to have SQL with result like

where 1=0 


so that SQL Server return the result in 10 secs?

For example, 
if parameter_table.col1 = 0, i get result from table_fee
Src     col1   fee   fee1
--------------------------------------------
null    USD    100   150
null    EUR    200   150
null    AUD    300   200


if parameter_table.col1 = 1, I get result from table_fee_detail
Src     col1   fee   fee1
--------------------------------------------
USA     USD    100   150
GER     EUR    200   150
AU      AUD    300   200
You are looking to choose rows from one of two tables dynamically. This is generally possible to achieve without dynamic SQL.

To demonstrate, here is a simplified version based on the AdventureWorks sample database:

The idea will be to choose rows from either the TransactionHistory or TransactionHistoryArchive tables for each Product, based on the value of the UseArchive column in a Parameter table:

CREATE TABLE dbo.Parameter
(
    ProductID integer NOT NULL PRIMARY KEY,
    UseArchive bit NOT NULL
);

INSERT dbo.Parameter
    (ProductID, UseArchive)
VALUES
    (1, 0),
    (2, 0),
    (3, 1),
    (4, 0);


This example specifies that rows should come from TransactionHistory for products 1, 2, and 4. For product 3, rows should come from TransactionHistoryArchive.

Solution:

SELECT 
    P.Name, 
    CA.TransactionID
FROM Production.Product AS P
JOIN dbo.Parameter AS PTR
    ON PTR.ProductID = P.ProductID
CROSS APPLY
(
    SELECT TH.ProductID, TH.TransactionID
    FROM Production.TransactionHistory AS TH
    WHERE 
        TH.ProductID = P.ProductID
        AND PTR.UseArchive = 0

    UNION ALL

    SELECT THA.ProductID, THA.TransactionID
    FROM Production.TransactionHistoryArchive AS THA
    WHERE 
        THA.ProductID = P.ProductID
        AND PTR.UseArchive = 1
) AS CA;


The execution plan for this query is:



The key here is the outer reference PTR.UseArchive = value inside the apply. This allows the optimizer to construct a plan featuring Filters with Start-Up Expressions. For example, the Filter above the TransactionHistory table Index Seek has:



The Filter Predicate above the other seek is very similar, of course, but tests UseArchive = 1.

Each Filter Predicate is evaluated once per product. It determines whether the seek below it in the plan will be executed on that iteration of the nested loop join.

In this example, the effect is that the TransactionHistory index seek is executed three times; the seek on the TransactionHistoryArchive table is executed just once.

You may be able to implement this pattern for your use case.