SQL Server – Most Efficient Way to Cancel or Avoid SELECT in UNION Query

sql serversql-server-2016

I have a query with the following structure. In some specific cases the query will return data only from 1 or 2 of the tables.

For example: 

DECLARE @Var1 int --Parameter in store procedure

SELECT several fields FROM T1 WHERE @Var IN(0,1) AND Conditions 
 UNION 
SELECT several fields FROM T2 WHERE @Var IN(0,1,2) AND Conditions 
 UNION
SELECT several fields FROM T3 WHERE @Var IN(0,2) Conditions

My question is regarding performance. What is more efficient, using one scalar variable as in the previous example or using IF sentences and create specific queries as in the example below?

IF @Var = 0
    SELECT several fields FROM T1 WHERE Conditions 
     UNION 
    SELECT several fields FROM T2 WHERE Conditions 
     UNION
    SELECT several fields FROM T3 WHERE Conditions 
ELSE 
 BEGIN
  IF @Var = 1
     SELECT several fields FROM T1 WHERE Conditions 
       UNION 
     SELECT several fields FROM T2 WHERE Conditions 
  ELSE 
     SELECT several fields FROM T2 WHERE Conditions 
       UNION
     SELECT several fields FROM T3 WHERE Conditions 
 END

I'd rather not to use the IF option because the query is quite big with several Fields and Conditions, however, I did check the execution plan of the first query and it shows an Index Scan.

I will appreciate any suggestion or comment

Best Answer

What is more efficient, using one scalar variable as in the previous example or using IF sentences and create specific queries as in the example below?

In SQL Server a query plan may not vary based on the parameters passed (unless you disable plan caching with OPTION RECOMPILE). So whenever you want different plans, you need seperate queries.

Related Question