SQL Server Performance

Which is better Table variable or Union?

Discussion in 'SQL Server 2005 General Developer Questions' started by monica4may, Apr 30, 2007.

  1. monica4may New Member

    I have a stored procedure expecting a $delimited input called type. For example type = '0$2$4$8$16$32'

    This sp has to fetch data from different tables based on the type and return in a single select.

    Should use table variables or should I go for a Union and IN Clause.

    First Scenario

    First I break the type variable and insert a record into a table variable InputType.
    Take another table variable OutputData

    -- get data for all types except 32
    Insert into OutputData
    Select * from DATA1 t1 INNER JOIN InputType t2
    on t1.type = t2.type and t1.type <> 32

    -- get data for type 32
    Insert into OutputData
    Select * from DATA2 t3 INNER JOIN InputType t2
    on t3.type = t2.type and t3.type = 32

    Select * from OutputData



    Second Scenario

    I replace the '$' with a ',' and also remove a particular type 32.
    -- so @type will be something like '0,2,4,8,16'
    Create a dynamic query

    EXEC ('Select * from DATA1 t1 INNER JOIN InputType t2
    on t1.type = t2.type and t1.type IN @type
    UNION
    Select * from DATA2 t3 INNER JOIN InputType t2
    on t3.type = t2.type and t3.type = 32')

    What do you suggest which one will be faster or can you provide a better way to handle the same.



  2. merrillaldrich New Member

    See Erland Sommarskog's web site for an article on this very topic

Share This Page