SQL – Temp Table v/s Temp Variable

Temp Tables are originated for the storage and it’s manipulation of temporal data. Also its stored physically created in tempDB.
Temp Variables are originated for returning date-sets from table-valued functions.

Temp tables might result in stored procedures being recompiled, perhaps often. Table variables will not.

Temp Table create using SELECT INTO, which can be quicker to write (good for ad-hoc querying) and may allow you to deal with changing datatypes over time, since you don’t need to define your temp table structure upfront.
Global Temp Tables (##tmp) are another type of temp table available to all sessions and users.

Temp table result can be used by multiple users.
Table variable can be used by the current user only. 

Temp table can do all the DDL operations. It allows creating the indexes, dropping, altering, etc..,
Table variable won’t allow doing the DDL operations. But the table variable allows us to create the clustered index only.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Create your website with WordPress.com
Get started
%d bloggers like this: