SQL Server 2008 Execution Plan Question

Question

I have a question addressed to sql guru.

There are two tables with almost the same structure.

Based on the parameter passed to the stored procedure, I need to collect data from a particular table.

How to do it in the best way?

Please do not suggest combining these tables into one, which is not suitable.

I did the 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 the execution plan, I was surprised because it received data from both tables in parallel threads and only after that filtered the @param value.

I thought that filtering would be done in the first step and the data collected from a separate table.

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

thank

Answer 1

T-SQL Erland Sommarskog. , - . , . SQL "" , , , , . . GO FOR SPEED ( ) . .: SQL

sql-server-2008, , SQL Server 2008 SP1 CU5 (10.0.2746) SQL 2008 R2 CU1 (10.50.1702) , ( " " ) OPTION(RECOMPILE), SQL 2008 2005 . @Local_Variables . UNION .

Answer 2

IF?

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

, , sp_executesql.

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

Answer 3

, , - . UNION UNION ALL. DISTINCT

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

        UNION ALL

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

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

Answer 4

SQL Server - , SQL ( ), ( ), . , SQL-, .

, SQL , . SQL, . , ( - ):

DECLARE @sql NVARCHAR(1000)

SET @sql = 'SELECT id, TypeCode, ProdCode'
IF @param = 1
   SET @sql = @sql + ' FROM table1'
IF @param = 2
   SET @sql = @sql + ' FROM table2'
SET @sql = @sql + ' WHERE TypeCode = ''INV'''

EXECUTE sp_ExecuteSQL @sql

, - , : sp_ExecuteSQL , - SQL - , .

Answer 5

tDUMMY ( ), .

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

 UNION ALL

        SELECT 
             id, TypeCode, ProdNo
        FROM 
             tDUMMY INNER JOIN Table2 ON TypeCode= 'INV'
        WHERE
             @param = 2
     ) m ON m.Id = s.Id 
WHERE s.Id = 256

tDUMMY, . , @param = 1, ( 1 tDUMMY, 2)

. UNION ALL ( ), .