CTE

CTE stands for Common Table expressions. It was introduced with SQL Server 2005. It is a short-term result set also it would be a result of complex sub-query. CTE improves readability and simple in maintenance of complex queries and sub-queries.

Ex:

    SELECT * FROM (
     SELECT Addr.Address, Emp.Name, Emp.Age From Address Addr
     Inner join Employee Emp on Emp.EID = Addr.EID) Temp
    WHERE Temp.Age > 50
    ORDER BY Temp.NAME

Temporary Tables

In SQL Server all the temporary tables are created inside Tempdb database at runtime where you can do all the operations which users can do on normal table

CREATE TABLE #Temp
(
 UserID int,
 Name varchar(50), 
 Address varchar(150)
)

insert into #Temp values ( 1, 'Shailendra','Noida');

Select * from #Temp

Table Variable

Alike Temporary table its is also created in the Tempdb database but not in the memory and It acts like a variable and exists for a particular batch of query execution.

 DECLARE @TProduct TABLE
 (

     SNo INT IDENTITY(1,1),
     ProductID INT,
     Qty INT
     ) 
     --Insert data to Table variable @Product 
     INSERT INTO @TProduct(ProductID,Qty)
     SELECT DISTINCT ProductID, Qty FROM ProductsSales ORDER BY ProductID ASC 
     --Select data
     Select * from @TProduct

     --Next batch
     Select * from @TProduct --gives error in next batch