To store some data temporarily, you can use either temporary table or table variable or CTE. By seeing these tables we get a question, what is the suitable option for our scenario.
Here, I am going to explain the difference between each of them.
Temporary table
Temporary tables are created in the tempdb. These tables are like normal tables you can have constraints and index.
This has two types:
1. Local Temporary table- These Tables are created with single hash ex: #Temp. This table exists for a particular session. This table created in one session and cannot be accessed in other session.
2. Global Temporary table-These Tables are created with double hash Ex: ## Temp. This table is accessible in all the session until the session the table was created is not closed.
Session is, when you opens a query window on management studio. Each query window you open is a 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. Table variable allows creating primary key at the time of declaration but can not create Non clustered index. This is very important detail to note that table variable is not affected from transaction.
CTE
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. CTE does not store data in tempdb rather it uses memory to store data. You cannot create any index on CTE.
More info can be found in the following link:
Common table expressions (CTE)
Note:
• Temp Table and Table Variable,both are created in TempDB and not in memory
• Scope of Temporary table is within the session
• Scope of table variable is within the batch
• Table variable is not affected from transaction
Here, I am going to explain the difference between each of them.
Temporary table
Temporary tables are created in the tempdb. These tables are like normal tables you can have constraints and index.
This has two types:
1. Local Temporary table- These Tables are created with single hash ex: #Temp. This table exists for a particular session. This table created in one session and cannot be accessed in other session.
2. Global Temporary table-These Tables are created with double hash Ex: ## Temp. This table is accessible in all the session until the session the table was created is not closed.
Session is, when you opens a query window on management studio. Each query window you open is a 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. Table variable allows creating primary key at the time of declaration but can not create Non clustered index. This is very important detail to note that table variable is not affected from transaction.
CTE
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. CTE does not store data in tempdb rather it uses memory to store data. You cannot create any index on CTE.
More info can be found in the following link:
Common table expressions (CTE)
Note:
• Temp Table and Table Variable,both are created in TempDB and not in memory
• Scope of Temporary table is within the session
• Scope of table variable is within the batch
• Table variable is not affected from transaction
References:
nice article sir, keep it up
ReplyDeletethanks regards
prashant wagare.