Table variables in SQL Server

Table variables:
• It is a datatype. Unlike other datatype, table variables cannot be used as input and output parameters. Its scope is Stored Procedure; User defined functions, and batches.
• It provides great performance when compared to temp tables.
• It is used instead of temporary tables, when the resultset is less than 100 rows. Hence if resultset is small, table variable is best choice.
• We can insert, delete, and update records into table variable.
• It has restricted scope, hence it brings performance optimization.
• In table variable, all constraints are used like unique, not null, check, default, primary key and unique key. Hence it produces the appropriate resultset.
• No need of recompilation of stored procedure while using table variables.
• During transactions, it produces less locking and logging overhead.
• No need to drop the table variable explicitly. It will be closed, once the application is closed.

Syntax:
DECLARE @tablevariable_name TABLE
{
[Column name (n) datatype]
}
INSERT INTO @tablevariable_name
SELECT * FROM table_name [where (condition)]
(SQL query statements: update or delete)

Sample code:
DECLARE @TibetanYaks TABLE
(YakID int,
YakName char (30)
)
INSERT INTO
@TibetanYaks (YakID, YakName)
SELECT
YakID, YakName
FROM  
dbo.Yaks
WHERE  
YakType = 'Tibetan'

Difference between Temporary table & Table variable:

Posted in: SQL Server Tutorial