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.
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