Difference Between Temp Table , Table Variable and CTE

Posted By : Bipul Kumar Tiwari,


Temp Table , Table variable and CTE are commonly used for storing data temporarily in SQL Server. It is a very commonly asked interview question.

1. Temporary Tables:

In SQL Server, temporary tables are created at run-time and you can do all the operations which you can do on a normal table. These tables are created inside Tempdb database. These tables are automatically destroyed at the termination of the procedure or session that created them. SQL Server contain two types of Temporary tables:
1.  Local Temporary Tables
2.  Global Temporary Tables

Local Temporary Tables:

Local temporary tables are temporary tables that are available only to the session that created them. These are automatically deleted when the session that created the tables has been closed. Local temp tables are Available only to the particular session for the user. If you will close the current query window or open a new query window and will try to find created temp table, it will give you the error.
local temp table

Global Temp Table:

Global Temporary tables name starts with a double hash ("##"). Global temporary tables are temporary tables that are available to all sessions and all users.  They are dropped automatically when the last session using the temporary table has completed.
global temp table

When to Use Temporary Tables


2. Temp Variables:

Temp Variables are also used for holding the data fora  temporary time just like Temp tables.  Temp Variables is created physically in the tempdb but acts like a variable . Temp variables are created using “DECLARE” statements and are assigned values by using either a SET or SELECT command.
temp variable

When to Use Temporary Tables


3. CTE (Common Table expressions):

CTE stands for Common Table expressions. It was introduced with SQL Server 2005. It works as a temporary result set that is defined within the execution scope of a single select, insert, update, delete statements. CTE is typically the result of complex sub queries. Similar to temporary tables CTE doesn’t store as an object .
CTE

When to Use Temporary Tables


Tabular Difference Between Temp Table , Table Variable and CTE

Local Temp Table Global Temp Table Table Variable Abstract Class
CREATE TABLE #temp (ID INT) CREATE TABLE ##temp (ID INT) CREATE TABLE ##temp (ID INT) ;WITH CTE_T AS (SELECT ID FROM table)
It created physically in the tempdb. It created physically in the tempdb. It created physically in the tempdb but acts like a variable. IT created in the memory.
It is Available only to the particular session. It is Available to all the sessions in that instance. IT is Available only to the particular batch in the session. It Available only to the particular scope in the session.
Table can be altered after creation. Table can be altered after creation. Table can NOT be altered after creation. CTE cannot be modified at all.
It Cannot be used in a view It Cannot be used in a view It Cannot be used in a view It Can be used in a view


Related Articles

 

About the Author

author
Its me BIPUL who is logically minded creative at heart , a good communicator , a self taught full stack developer. I constantly focuses on my thinking , reading , collecting and creating my work in a order to enhance my skills.

I discover new dimensions for growing bussiness with a proven record in creating database and programming. I have a strong technical skills as well as strong interpersonal skills. Read more...
 

Browse By Category

Popular Articles