Using Table Variable in SQL
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:
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.
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;
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.