Table variable in SQL

Kailash Chandra Behera | Sunday, February 05, 2017

Introduction

The article defines SQL Table variable, why it is introduced, how difference it from SQL temp table.

Getting Started

A table variable contains a collection of data or records. It can be used in batches, stored procedures, and user-defined functions. We can UPDATE records in our table variable as well as DELETE records. The variable will no longer exist after the procedure exits and there is no need to drop like a tamp table.

As like normal table and temp table, constraints(primary key, check etc.) and identity column can be used with table variable. Declaration syntax of table variable is very similar to a CREATE TABLE statement.

 DECLARE @Student TABLE  
 (  
    ID int,   
    Name NVARCHAR(100)  
 )
Unlike temp tables, table variables when using inside store procedure can avoid this recompilation hit, because using a temp table in store procedure may result in additional re-compilations of the stored procedure. It will generally use fewer resources than a temporary table.

So far it seems that table variables can do anything temporary tables can do within the scope of a stored procedure, batch, or UDF), but there are some drawbacks.

SQL Server does not maintain statistics on a table variable, and statistics are used heavily by the query optimizer to determine the best method to execute a query. Neither of these restrictions should be a problem, however, as table variables generally exist for a specific purpose and aren’t used for a wide range of ad-hoc queries.

Even a non-clustered index can not be created on a table variable unless the index is a side effect of a PRIMARY KEY or UNIQUE constraint on the table (SQL Server enforces any UNIQUE or PRIMARY KEY constraints using an index).

After the DECLARE statement of the table variable, its definition cannot be changed. ALTER TABLE query attempting to alter a table variable will fail with a syntax error. Along the same lines. You cannot use a table variable with SELECT INTO or INSERT EXEC queries. for use in a join, you will need to alias the table in order to execute the query.

Thanks

By at