SQL Server 2008 execution plan question

Question

I have a question addressed to sql guru.

There are two tables with almost identical structure.

Based on parameter passed into the stored procedure I need to collect data from one or another table.

How to do that in a best way?

Please do not suggest to combine those tables into single one - that is not appropriate.

I did it following (MS SQL Server 2008):

Select *
FROM
    String s
    JOIN (
        SELECT id
            ,TypeCode
            ,ProdNo         
                FROM Table1
        WHERE @param = 1 AND TypeCode= 'INV'

        UNION

        SELECT id
            ,TypeCode
            ,ProdNo
        FROM Table2
        WHERE @param = 2 AND TypeCode= 'INV'

    ) m ON m.Id = s.Id
WHERE s.Id = 256

but when I looked at execution plan I was surprised because it got data from both tables in parallel threads and only after that filtered by @param value.

I thought that filtering will be made on the first stage and data collected from single table.

Is there a way to make select only from one table without splitting query into two queries and using IF operator?

Thanks

Answer 1

you really need to read this Dynamic Search Conditions in T-SQL by Erland Sommarskog. You shouldn't worry about repeating code, this is not some homework assignment. Just worry about making the execution plan use an index. When making SQL code "pretty" the only thing to consider is indenting & case, any other changes can cause the query plan to be slower. I've seen trivial changes to a super fast query result in a super slow query. GO FOR SPEED (index usage) and duplicate code as necessary. also see: The Curse and Blessings of Dynamic SQL

You tagged the question sql-server-2008, so if you're running SQL 2008 SP1 CU5 (10.0.2746) and SQL 2008 R2 CU1 (10.50.1702) and later, there is a new behavior (as explained in the "Dynamic Search Conditions" article linked above) of OPTION(RECOMPILE) that does not appear in all versions of SQL 2008 or in 2005. This behavior basically evaluates the @Local_Variables values at runtime and recompiles the query accordingly. In your case, this should cause one half of your UNION to be eliminated when compiling them.

Answer 2

Could you just use a simple IF statement?

IF @Param = 1 
  BEGIN
    EXEC SQL
  END
ELSE IF @Param = 2
  BEGIN
    EXEC SQL
  END 
ELSE
  RAISERROR('Invalid Parameter', 16, 1)

Or alternatively you could build the query dynamically and execute it using the sp_executesql stored procedure.

DECLARE @Sql NVARCHAR(100)

SET @Sql = N'SELECT * FROM ('

IF @Param = 1
  SET @Sql = @Sql + N'SELECT 1 a, 2 b, 3 c'
ELSE IF @param = 2
  SET @Sql = @Sql + N'SELECT 4 a, 5 b, 6 c'
ELSE
  RAISERROR('Invalid Parameter', 16, 1)

SET @Sql = @Sql + ') tbl'
EXEC sp_executesql @sql