Knowledge is no fun, unless you share it!!! :)

Tuesday, 1 October 2013

Table Variables in SQL Server 2012 and differences with Temporary Tables

Table variable is handy feature of SQL Server which lets you define a variable as a table and then use it in SQL just like any other table.
Table variables were introduced in SQL Server 2000.

Temporary tables are also exists for same purpose in SQL-Server.
So there are some basic concept about usage of these two component of SQL-Server (Temporary Tables and Table Variables)

We can optimize our store procedure by repacing these components on the basis of there usages.
So today we will What is Table variables and what are the diffrence between Temporary Table and Table Variable.

SQL Server provides an variable known as table variable which is used to store data in a similar way as we store data in physical tables but with some limitations.
In SQL Server you can use temporary tables to store intermediate results. This is a common used technique to speed up query processing. Recently I came across a problem where the temporary tables were causing the performance degradation.
 
Table Variable:

You create a table variable the same way you create any other variable:
using the declare statement:

declare @TableVar table (CustomerID nchar(5) NOT NULL)

This declares a table variable named @TableVar that we can use in place of a temporary table. You can use a table variable for just about anything you'd use a regular table.
The following statements won't work however:

INSERT INTO table_variable
EXEC stored_procedure SELECT select_list INTO table_variable

We can write below piece of code inside store procedure:

DECLARE @ttable (idintIDENTITY,orderid int)
INSERT INTO @t(orderid)
SELECT a.OrderID FROM Database.TableA as a
INNER JOIN Database.TableB as b ON
a.ID = b.ID


For more diffreces you can go though the nice article written by Tejas Vashnav -
http://www.codeproject.com/Articles/415184/Table-Variable-V-S-Temporary-Table

Your suggestions is appriciated ! ! !
Next time we will learn about CTE (Common Table Expression) and it's usages.