Table Variables In T-SQL

Table Variables In T-SQL

Posted byonSonntag, 20. März 2005

Table variables allow you to store a resultset in SQL Server without the overhead of declaring and cleaning up a temporary table. In this article, we will highlight the features and advantages of the table variable data type.

Microsoft introduced table variables with SQL Server 2000 as an alternative to using temporary tables. In many cases a table variable can outperform a solution using a temporary table, although we will need to review the strengths and weaknesses of each in this article.

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

)

While connected to the Northwind data-base, we could 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

You can use table variables in batches, stored procedures, and user-defined functions (UDFs). We can UPDATE records in our table variable as well as DELETE records.

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

You might think table variables work just like temporary tables (CREATE TABLE #ProductTotals), but there are some differences.

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s