CTE, Temp table and table

Question

Can someone tell me where to use CTE , temp table and table variable ?

I read about their differences, but I am confused by their use. please, help.

Thanks.

Answer 1

You can use CTE instead of an auxiliary query or when you need recursion.

CTE is only available during the SQL statement that includes it. Previous and subsequent statements will not have access to him and will not see him. It behaves like a subquery, but can be used several times in the next select / update.

This query is with a subquery with an additional query used twice:

 Select D.* From D Inner 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+1 Inner 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 

Can be replaced by CTE:

 ; 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 D Inner Join CTE as c1 on c1.id = D.id+1 Inner Join CTE as c2 on c2.id = D.id-1 

This is useful in this case because the same subquery does not need to be written multiple times.

Recursive CTE (this is just an example, it should not be a SQL Server job to manage string data like this):

 Declare @data varchar(50) = 'Recursive CTE' ; With list(id, letter) as ( Select 1, SUBSTRING(@data, 1, 1) Union All Select id+1, SUBSTRING(@data, id+1, 1) From list Where id < len(@data) ) Select * from list 

A recursive CTE can be used to retrieve data in a hierarchy.

Table variables

Table variables exist only during query execution. It is displayed for all SQL statements after it is created.

You can use them when you need to pass data to a stored procedure or function using a table type parameter:

 Create Proc test( @id int, @list table_type_list READONLY ) begin set nocount on select * from @list end Declare @t table_type_list Insert into @t(name) values('a'), ('b'), ('c') Exec test 1, @t 

You can also use them when you need to store something that is not too large and does not require indexes. You cannot manually create an index, although a primary key or a unique constraint in a table declaration will automatically create an index.

There are no statistics created in the table variables, and you cannot create statistics.

Pace table

The Temp table can be used when you are dealing with much more data that will benefit from creating indexes and statistics.

In a session, any operator can use or modify the table after its creation:

 create table #temp Insert into #temp(...) select ... From data exec procA exec procB exec procC 

Both ProcA, ProcB, and ProcC can select, insert, delete, or update data from #temp.

The #temp table will be deleted as soon as the user session is closed.

If you do not want to maintain a temporary table between sessions, you can use the global temp table (## temp). It will be available until it crashes or the server restarts.