Social Networks

Table Variable in SQL Server like other vaiable in the SQL Server is a very useful programming construct. Table variables store a set of records syntax looks very similar to a CREATE TABLE . The scoping rules of the table variables are similar to any other programming variables. For example, if you define a variable inside a stored procedure, it can’t be accessed outside the stored procedure. When you create a table variables, it is like a regular DDL operation and its metadata is stored in system catalog. Table variable is not a temporary table.

Table Variable in SQL Server

Declare Table Variables:

DECLARE @ProductTotals TABLE
(
ProductID int,
Revenue money
)

Populate the table variables:

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

 

Operation on Table variables:

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

New feature in SQL Server 2008:

A table variable can be very powerful when used with stored procedures to pass it as input/output parameters (new functionality available starting with SQL Server 2008) or to store the result of a table valued function.

Limitations:

No DDL operation allowed on temporary table
SQL Server does not maintain statistics on a table variable
Temporary Tables and Table Variables:
Table variable is always the optimum choice

 

 

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *

Allowed Tags:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>