SQL Server temporary tables, table scalars, and CTE

Temporary tables, table variables, and CTE are commonly used in SQL Server to store temporary table data, and here is a brief introduction to the different and different scenarios in which they are used.

Cte

A CTE is often called a "generic expression" and is created in memory.
Purpose: Typically used to replace subqueries that require recursion.
Valid range: CTE can only be used in statements that contain him.
For example: In some complex query statements, subqueries appear multiple times, so that the code is lengthy and inefficient to execute:

Select D.* From DInner Join (        Select id value, date From A        Inner Join B on A.data < B.date        Inner Join C on C.data > B.date    ) CTE a c1 on c1.id = D.id+1Inner Join (    Select id value, date From A    Inner Join B on A.data < B.date    Inner Join C on C.data > B.date) as c2 on c2.id = D.id-1

Replace subqueries with a CTE, save complex subquery results, and reduce the number of queries

with CTE as (    Select id value, date From A    Inner Join B on A.data < B.date    Inner Join C on C.data > B.date)Select D.* From DInner Join CTE as c1 on c1.id = D.id+1Inner Join CTE as c2 on c2.id = D.id-1

Temp table

In SQL Server, temporary tables are created at run time, and you can perform all the operations that you can perform on the normal table. These tables are created in the Temdb database. Depending on the scope and behavior, the staging table is divided into two types, as follows

1. Local Temp Table
   Local temporary tables are only available for SQL Server sessions (connections) that create tables, and are automatically deleted when the connection is closed. The declaration is prefixed with ' # '.
   Note the session here, the database refers to the same open query window, and the ASP. NET program that calls it: one sqlconnection connection (not a session in ASP.).
   Create a new local temporary table and insert data

create Table #LocalTemp(    UserID int,    Name varchar(50),    Address varchar(150))goinsert into #LocalTemp values(1,‘Shailendra‘,‘Noida‘)

Point to query in the current window
select * from #LocalTemp;

1. Global temp Table
   Global temporary tables are available to all SQL Server sessions or connections (that is, all users). These tables can be created by any SQL Server connection user and are automatically deleted when the connection to create the temporary table is closed. The declaration is prefixed with ' # # '.
   Create a new global temporary table and insert data

create Table ##GlobalTemp(    UserID int,    Name varchar(50),    Address varchar(150))goinsert into ##GlobalTemp values(1,‘Shailendra‘,‘Noida‘)

Also open a new window (or a new user connection) can also be queried normally
select * from ##GlobalTemp;

Table variables

This is like a variable, and exists in a particular batch of query execution. Once it is out of the batch, it is deleted. This is also created in the Temdb database, not memory. This also allows you to create a primary key, an identity, and not a nonclustered index when a table variable is declared.
It is shown here as a variable that can be passed into a stored procedure as a parameter
Declaring a table variable type

CREATE TYPE table_type_list AS TABLE (    name varchar(50))GO

To create a stored procedure that receives table variables

Create Proc test(    @id int,    @list table_type_list READONLY)asbegin    set nocount on    select * from @listend

Declares a table variable and inserts data into a table variable to execute the stored procedure

Declare @t table_type_listInsert into @t(name) values(‘a‘), (‘b‘), (‘c‘)Exec test 1, @t

SQL Server temporary tables, table scalars, and CTE