CTE,Temp table and table variable

Question

Can any one please tell me where to use CTE, temp table and table variable?

I read about their differences but i'm confused with their usage. please help.

Thanks.

Bausch

Answer 1

You can use a CTE in place of a sub query or when you need recursivity.

The CTE is only available during the SQL statement which includes it. Preceding and following statements won't have access to it and won't see it. It behave like a subquery but can be used several time in the following select/update.

This query with sub query with a sub 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

Could be replaced by a 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 usefull in this case because the same sub query does not have to be written several times.

Recursive CTE (this is just an example, this should not be SQL Server job's to manipulate 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

Recursive CTE can be used to retrieve data in a hierachy.

Table variables

Table variables only exist while the query is being executed. It is visible to all SQL statements after its creation.

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 which is not too big and does not requiere indexes. You cannot manually create an index although a primary key or a unique constraint in the table declaration will automatically create an index.

There are no statistics created on table variables and you cannot create statistics.

Temp Table

Temp table can be used when you are dealing with a lot more data which will benefit from the creation of indexes and statistics.

In a session, any statement can use or alter the table once it has been created:

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.

Table #temp will be drop as soon as the user session is closed.

If you wan't to keep the temp table between sessions, you can use a global temp table (##temp). It will be available until it is drop or the server is restarted.

Lida