Table variable in SQL Server

Question

I am using SQL Server 2005. I have heard that we can use a table variable to use instead of LEFT OUTER JOIN.

What I understand is that, we have to put all the values from the left table to the table variable, first. Then we have to UPDATE the table variable with the right table values. Then select from the table variable.

Has anyone come across this kind of approach? Could you please suggest a real time example (with query)?

I have not written any query for this. My question is - if someone has used a similar approach, I would like to know the scenario and how it is handled. I understand that in some cases it may be slower than the LEFT OUTER JOIN.

Please assume that we are dealing with tables that have less than 5000 records.

Thanks

Answer 1

In my opinion there is one reason to do this: If you have a complicated query with lots of inner joins and one left join you sometimes get in trouble because this query is hundreds of times less fast than using the same query without the left join.

If you query lots of records with a result of very few records to be joined to the left join you could get faster results if you materialize the intermediate result into a table variable or temp table.

But usually there is no need to really update the data in the table variable - you could query the table variable using the left join to return the result.

... just my two cents.

Answer 2

Thank you, astander.

I tried with an example given below. Both of the approaches took 19 seconds. However, I guess some tuning will help the Table varaible update approach to become faster than LEFT JOIN.

AS I am not a master in tuning I request your help. Any SQL expert ready to prove it?

---

---- PLease replace "" with '' below. I am not familiar with how to put code in this forum... It causes some troubles....

CREATE TABLE #MainTable ( 
        CustomerID INT  PRIMARY KEY, 
        FirstName VARCHAR(100) 
) 


DECLARE @Count INT
SET @Count = 0

DECLARE @Iterator INT
SET @Iterator = 0

WHILE @Count <8000
BEGIN

    INSERT INTO #MainTable SELECT @Count, "Cust"+CONVERT(VARCHAR(10),@Count)
    SET @Count = @Count+1

END

CREATE TABLE  #RightTable 
( 
        OrderID INT  PRIMARY KEY, 
        CustomerID INT, 
        Product VARCHAR(100) 
) 

CREATE INDEX  [IDX_CustomerID] ON #RightTable (CustomerID)

WHILE @Iterator <400000
BEGIN

        IF @Iterator % 2 = 0
        BEGIN

            INSERT INTO #RightTable  SELECT @Iterator,2, "Prod"+CONVERT(VARCHAR(10),@Iterator)
        END
        ELSE
        BEGIN
            INSERT INTO #RightTable  SELECT @Iterator,1, "Prod"+CONVERT(VARCHAR(10),@Iterator)
        END

SET @Iterator = @Iterator+1

END


-- Using LEFT JOIN

SELECT  mt.CustomerID,mt.FirstName,COUNT(rt.Product) [CountResult]
FROM    #MainTable mt 
        LEFT JOIN #RightTable rt ON mt.CustomerID = rt.CustomerID 
GROUP BY mt.CustomerID,mt.FirstName


---------------------------



-- Using Table variable Update


DECLARE @WorkingTableVariable TABLE
( 
        CustomerID INT, 
        FirstName VARCHAR(100),
        ProductCount INT  
) 

INSERT 
INTO @WorkingTableVariable (CustomerID,FirstName)
SELECT  CustomerID, FirstName FROM #MainTable 

UPDATE  @WorkingTableVariable 
SET     ProductCount = [Count]
FROM    @WorkingTableVariable wt 
        INNER JOIN
            (SELECT CustomerID,COUNT(rt.Product) AS [Count]
             FROM #RightTable rt
             GROUP BY  CustomerID) IV ON wt.CustomerID = IV.CustomerID 

SELECT CustomerID,FirstName, ISNULL(ProductCount,0) [CountResult] FROM    @WorkingTableVariable 
ORDER BY CustomerID

--------

DROP TABLE  #MainTable

DROP TABLE  #RightTable

Thanks Lijo

Answer 3

It can be done, but I have no idea why you would ever want to do it.

This realy does seem like it is being done backwards. But if you are trying this for your own learning only, here goes:

DECLARE @MainTable TABLE(
        ID INT,
        Val FLOAT
)

INSERT INTO @MainTable SELECT 1, 1
INSERT INTO @MainTable SELECT 2, 2
INSERT INTO @MainTable SELECT 3, 3
INSERT INTO @MainTable SELECT 4, 4

DECLARE @LeftTable TABLE(
        ID INT,
        MainID INT,
        Val FLOAT
)

INSERT INTO @LeftTable SELECT 1, 1, 11
INSERT INTO @LeftTable SELECT 3, 3, 33

SELECT  *,
        mt.Val + ISNULL(lt.Val, 0)
FROM    @MainTable mt LEFT JOIN
        @LeftTable lt ON mt.ID = lt.MainID

DECLARE @Table TABLE(
        ID INT,
        Val FLOAT
)

INSERT INTO @Table
SELECT  ID,
        Val
FROM    @MainTable

UPDATE  @Table
SET     Val = t.Val + lt.Val
FROM    @Table t INNER JOIN
        @LeftTable lt ON t.ID = lt.ID

SELECT *
FROM    @Table

Answer 4

Every scenario is different, and without full details on a specific case it's difficult to say whether it would be a good approach for you.

Having said that, I would not be looking to use the table variable approach unless I had a specific functional reason to - if the query can be fulfilled with a standard SELECT query using an OUTER JOIN, then I'd use that as I'd expect that to be most efficient.

The times where you may want to use a temp table/table variable instead, are more when you want to get an intermediary resultset and then do some processing on it before then returning it out - i.e. the kind of processing that cannot be done with a straight forward query.

Note the table variables are very handy, but take into account that they are not guaranteed to reside in-memory - they can get persisted to tempdb like standard temp tables.

Answer 5

I don't think it's very clear from your question what you want to achieve? (What your tables look like, and what result you want). But you can certainly select data into a variable of a table datatype, and tamper with it. It's quite convenient:

DECLARE @tbl TABLE (id INT IDENTITY(1,1), userId int, foreignId int)

INSERT INTO @tbl (userId)
    SELECT id FROM users
    WHERE name LIKE 'a%'

UPDATE @tbl t
SET
    foreignId = (SELECT id FROM foreignTable f WHERE f.userId = t.userId)

In that example I gave the table variable an identity column of its own, distinct from the one in the source table. I often find that useful. Adjust as you like... Again, it's not very clear what the question is, but I hope this might guide you in the right direction...?