CTE, Temp table, Global temp table, Table variable

CTE

1. CTE is Common table expression
2. Temporary result set
3. Used for storing result of complex queries
4. This table available only for the current query

When to use

To store result of complex queries
To create recursive query

Example:

;With CTE1(Name, Roll, Age)--Params are optional
AS
(
SELECT Student.Name, Student.Roll, Student.Age from Student
)
SELECT * FROM CTE1 --Using CTE 
WHERE CTE1.Age > 50
ORDER BY CTE1.NAME

Local Temp Table

1. This table available only for one sql server session or connection that created this table.
2. Drop automatically when session ends or connection close.
3. These tables are available to one tab only once created
4. These table starts with # sign

Example:

    CREATE TABLE #LclTmp
    (
     ID int,
     Name varchar(50), 
     Address varchar(150)
    )
    GO
    insert into #LclTmp values ( 1, 'Tejpal','jammu');
    GO
    Select * from #LclTmp 

Global Temp table

1. This table available for all sql server sessions or connections.
2. Drop automatically when all sessions have been closed.
3. These tables are available to all tabs of sql server once created.
4. These table starts with Double ## sign.

Example:

    CREATE TABLE ##GblTmp
    (
    UserID int,
    Name varchar(50),
    Address varchar(150)
    )
    GO
    insert into ##GblTmp values ( 1, 'Tejpal','jammu');
    GO
    Select * from ##GblTmp

Table variable

1. This table available for a group of query execution.
2. Drop automatically when all the queries of the group are executed
3. These tables are declared with @ sign

Example:

    GO
    DECLARE @TableVariable TABLE
    (
    Id INT IDENTITY(1,1),
    GoodsID INT,
    Quantity INT
    )
    --Insert data to Table variable @Tablevariable
    INSERT INTO @TableVariable(GoodsId,Quantity)
    SELECT DISTINCT ID, Quantity FROM GoodsSales ORDER BY GoodsID ASC
    --Select data
    Select * from @Tablevariable

    --Next batch
    GO
    Select * from @TableVariable--gives error in next batch