Rankspike

Table variables was introduced by Microsoft in SQL Server 2000 as an alternative way to using temporary tables.
In most cases table variable outperforms temp table, in this post we will try to review each strength and weaknesses.

Table variables store a set of records, so naturally the declaration syntax looks very similar to a CREATE TABLE statement, as you can see in the following example:

Code block

DECLARE @ProductTotals TABLE
(
  ProductID int,
  Revenue money
)

Let’s try, connect to your Northwind data-base (as my favorite test ground db), and write the following SELECT statement to populate the table variable.

Code block

INSERT INTO @ProductTotals (ProductID, Revenue)
  SELECT ProductID, SUM(UnitPrice * Quantity)
    FROM [ORDER Details]
    GROUP BY ProductID

As you may have theorize Table variables can be used in batches, stored procedures, and user-defined functions (UDFs). We can also UPDATE records in our table variable as well as DELETE records, here is an example;

Code block

UPDATE @ProductTotals
  SET Revenue = Revenue * 1.15
WHERE ProductID = 62
 
DELETE FROM @ProductTotals
WHERE ProductID = 60
 
SELECT TOP 5 *
FROM @ProductTotals
ORDER BY Revenue DESC
    SET @INDEX = CHARINDEX(@delimiter , @text) 
    IF (@INDEX = 0) AND (LEN(@text) > 0) 
      BEGIN  
        INSERT INTO @Strings VALUES (@text)
          BREAK 
      END 
    IF (@INDEX > 1) 
      BEGIN  
        INSERT INTO @Strings VALUES (LEFT(@text, @INDEX - 1))  
        SET @text = RIGHT(@text, (LEN(@text) - @INDEX)) 
      END 
    ELSE
      SET @text = RIGHT(@text, (LEN(@text) - @INDEX))
    END
  RETURN
END
 
GO
 
SET QUOTED_IDENTIFIER OFF
GO
 
SET ANSI_NULLS ON
GO

Personally, I find it easy to use temp table for quick and dirty solution but for more complex queries, especially reports, table variable comes in handy, as programmer you pick the right solution for your needs.