T-SQL/MSBI Knowledge Share Videos

Temp table,Table variable,CTE

There might be a situation where you need to store some data temporarily, in that case u can use either temporary table or table variable or CTE(Common Table Expression). By seeing these tables we get a question in our mind, What is the difference between each of these and when to use each  of these. Here goes the explanation.

Temporary table:
These are the tables created physically in the tempdb. You can create this table same as the normal table and   also this table can have constraints, index like normal table. Again this is classified into two types:
  • Local Temporary table
  • Global Temporary table
Local temporary tables are created with single hash i.e. #. Ex: #Temp or #Anyname. This table exists for a particular session. Now what is a session. Session is, when u open the management studio you open a query window. Each query window you open is a session. So this table created in one session cannot be accessed in other session.

Now go to another query window and execute the below query,the session id changes and u will get an error message.

SELECT * FROM tempdb.dbo.sysobjects WHERE name LIKE '%#TempSample%'
SELECT @@SPID AS SessionID
SELECT * FROM TempSample

(1 row(s) affected)
(1 row(s) affected)
Msg 208, Level 16, State 1, Line 5
Invalid object name '#TempSample'.

So once the query window where u created the #temp table is closed #temp table gets dropped by its own. So #temp exists for a particular session until that session exists.

Global temporary tables are created by double hash i.e. ## Ex: ##TempSample or ##Anyname. This table is accessible in all the session untill the session the table was created is not closed. Once the query window where the ## table was created is closed the table gets dropped on its own.


Note : Scope of Temporary table is within the session.

Table variable:
This is just like a variable. This table exists for a particular batch of execution. It gets dropped once it comes out of batch. This table is created in tempdb but not the memory.
In the below example you can see that there is a GO after the first select * from sys.tables and then create the table variable and then immediately again select * from sys.tables without GO. Since Go represents the batch the table exists for a batch. So you notice that in the first select * from sys.tables there are 12 entries but in the second one you notice 13 entries in sys.tables. So it means table variables are stored in the tempdb for that batch of execution and later once batch execution is complete the table is dropped. Here the tempdb do not hold the table with its original name instead you can see #BD763C6F.
Table variable allows to create primary key at the time of declaration as below:
DECLARE @TempSample TABLE (ID INT PRIMARY KEY,NAME VARCHAR(40))

Cannot create Non clustered index on Table variable.
Note: Scope of table variable is within the batch.


CTE(Common Table Expression):
This is just like a view which has a base table,mainly used to manipulate the base tables data. This exists for the scope of statement. It can be used as recursion. This do not store data in tempdb rather it uses memory to store data. You cannot create any index on CTE . In the below fig you can see tempdb sysobjects do not have any record even though CTE table is created. In the below example you can see that the number of records in sys.tables remains same before the CTE and after the CTE is created. This means when CTE is executed no entry is made in the tempdb.






































More info can be found in the following links:

http://blog.sqlauthority.com/2009/12/15/sql-server-difference-temptable-and-table-variable-temptable-in-memory-a-myth/

http://support.microsoft.com/kb/305977

5 comments:

Anonymous said...

Sreehari,

Thank you for explaining the difference. I am studying TSQL and your explanation really helped. YOU ARE A GENIUS! :)

~Geoffrey Ashna

Sreehari Katageri said...

Geoffrey Ashna,

Thank you.
If you found any mistakes in my posts or want to share anything new on TSQL, you are welcome.

Thanks,
Sreehari

Anonymous said...

From a developer's point of view:

Can you tell me what scenario can the following functions/objects be best used:

Temp tables

Table Variables

CTE's

Basically what scenario would be best used by a temp tables and not be a table variable...or what scenario would be best used with a CTE and not a temp table...and most importantly WHY...is it best?

Thanks,

Geoffrey

Sreehari Katageri said...

Hi Geoffrey,

Basically if the data size is huge then Temp tables are used. Here you can even create index, primary key. It is just like a normal table kind of.

In case of Table variable ,it is used if the data size is small. here you cannot create index. Initially the data is stored in memory and as the data size exceeds the memory it is stored on the disk.

CTE is used when you want the result set to be used in the consecutive queries more than once or if you need recursive queries.

Sreehari Katageri said...

Hi Geoffrey,

please look into :
http://skatageri.blogspot.in/p/miscellaneous.html
http://skatageri.blogspot.in/p/miscellaneous.html