Temp tables in SQL | Complete Tutorial
This demo includes
1) Temp tables in SQL
2) Common Table Expression in SQL Server (CTE)
3) Recursive CTE
4) Table Variable
5) Local Temporary Tables aka #Temp Tables in SQL Server
6) Global Temporary Tables aka ##Temp Tables in SQL Server
CTE (Common Table Expression) In SQL Server
--------------------------------------------------------------------------
CTE in sql server is used to name the result set so that you can use it later in SQL Server. CTE in SQL server makes the code a lot easier to Read. To Create CTE in SQL Server, We have to wrap the select statement with ;With CTE (select Statement) and after that we can use select, INSERT, Update and Delete.
Recursive CTE in SQL Server
-----------------------------------------------
Recursive CTE is created from SQL Server CTE. It will have two parts. Anchor part and recursive Part. Anchor Part will be executed first and later the Recursive part will be executed in Recursive CTE in SQL Server.
Table Variable in SQL Server
-----------------------------------------------
Table Variable is a special kind of variable which is used to create a temporary table. The scope of the table variable is up to the current session only. Table Variable will be stored in memory but if the data becomes use SQL servers pushes the Table Variable to Temp Db. Transactions will not impact Table Variable in SQL Server. We can add indexes in Table Variable. We cannot alter table variable.
Local Temporary Table in SQL Server
-----------------------------------------------------------
Local Temporary tables in SQL server are similar to the physical tables but they will be stored in Temp database. Local Temporary tables or #Temp tables are available for current session only. We can alter Local Temporary Tables or #Temp Tables. We can INSERT, DELETE, UPDATE and SELECT Local Temporary Tables or #Temp tables.
Global Temporary Table in SQL Server
--------------------------------------------------------------
Global Temporary Tables or ##Temp tables in SQL server are similar to the physical tables but they will be stored in Temp database. Unlike Local Temporary Tables in SQL Server, Global Temporary tables or ##Temp tables will be available for any number of sessions. We can INSERT,DELETE, UPDATE and SELECT Global Temporary tables or ##Temp Tables in SQL Server.
--------------------------------------------------------------
We can use Temporary Tables in SQL Server to extract the result set and do some operations on the result set. Every type of Temporary Table in SQL server has its own advantages and disadvantages so based on the requirement we have to decide on using Temporary tables in SQL Server.
-------------------------------------------------
Chapters
--------------------
00:00 Introduction
02:00 Common Table Expression (CTE In SQL Server)
05:00 Recursive CTE in SQL Server
23:40 Table Variable in SQL Server
29:16 Local Temporary (#Temp Table) Table in SQL Server
34:09 Global Temporary (##Temp Table) Table in SQL Server