Temp Table And Table Variable In SQL Server

Introduction

In this article i will explain about what is Temp table and Table variable ,why do we need to use these with our procedures and comparison between both temp table and table variable.

What TempTable Is

Temp or Temporary table is just like your normal Sql table but it has few restrictions that's why we did not use this as regular table. If we are working with large database and with many tables then we have relationship between tables to maintain the whole data. But by using temp table there is no concept of foreign key constraint means you can not use any foreign key constraint with it. 

As its name suggest it is temporary. Temporary tables automatically dropped when procedure finishes execution. Means doesn't matter how many tables created by you in a single procedure. They all are dropped after completion of execution.

Syntax

CREATE TABLE #YourTable     Field1 DataType     Field2 DataType     .................. GO

Sql server defines # ( Has or pond) Sign for Temp table declaration.

Example

CREATE TABLE #MyTempTb     Id INT     Name VARCHAR(50)     Phone INT

Insert Values With in temp table

INSERT INTO #MyTempTb values ( 1, 'JP', 7838000001) INSERT INTO #MyTempTb values ( 2, 'GN', 9456000001)

Select data from temp table

SELECT ID , Phone  FROM  #MyTempTb

All the operational syntax are same just like our main table but only difference is that we use # sign for temp table.

Table variable

Table variable also same as temp table. We need to define our table variable means a table type variable of variable instead of creating a table like temporary table.

It is good practice to use Table variable in our procedures . but if we have more than 150 n above rows than my opinion is use the temporary table , It is easy to handle data b indexing using temporary table. 

Defining a Table variable

DECLARE @tblVar table( ID int, Name VARCAR(50))

Insert and Selection is same as Temporary tables.

INSERT INTO @tblVar values ( 1, 'JP', 7838000001) INSERT INTO @tblVar values ( 2, 'GN', 9456000001)

DELETE 

DELETE from @tbvar

Global Temporary Table

Global temporary table are visible to all SQL server connections. Means I you create any global temp table than it will be visible to all user's. It is rarly used in SQL server. It has 2 has '#' sign for declaration.

CREATE TABLE # #globalTb     Field1 DataType     Field2 DataType     .................. GO

Intresting Points

Both temp table and variable are stored in tempdb.

You can not truncate a table variable because it is not a physical table.
You can not perform any DDL operation with table variable like Alter table, Create Index and Truncate.
You can not use foreign key constraint with temp table