Temporary Tables vs Table Variable in SQL Server 2008

Types of Temporary Tables

1. Local Temp Table
2. Global Temp Table

Local temp tables are only available to the current connection and are prefixed by "#".

Global Temp tables are available to any user by any connection and are prefixed by "##".

Syntax For Local temp tables

CREATE TABLE #MyLocalTempTable(
iStudentID int,
vchName varchar(50))

Syntax For Global Temp tables

CREATE TABLE ##MyGlobalTempTable(
iStudentID int,
vchName varchar(50))

Where Are Temporary Tables Stored?



In Which Cases Are Temporary Tables Used?

1. When we are doing a large number of row manipulations in Stored Procedures.
2. This is useful to replace the cursor.

Table Variable

Step 1 : Create Table Variable

Declare @TempTableVariable TABLE(
StudentID int,
Name varchar(50)
)

Step 2 : Insert data

insert into @TempTableVariable values ( 1, 'Yogesh');
insert into @TempTableVariable values ( 2, 'Deepak');

Step 3 : Select Data

select * from @TempTableVariable



For which cases are Table Variables used?

They are always useful for less data. If you have a large amount of data then go for a temp table.

Happy Coding