Table variable in SQL


Introduction

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

Getting Started

A table variable contains collection of data or record. 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 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 table a table variables when using inside store procedure can avoid this recompilation hit, because using 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 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 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 using in a join, you will need to alias the table in order to execute the query.

Thanks
Kailash Chandra Behera


1 comment:

  1. online casino - Play for free, no deposit bonuses - KDAP
    Online casino games are the result of 메리트 카지노 고객센터 a complex game play-and-win strategy. Players have 바카라 various options to play games and kadangpintar

    ReplyDelete