Rollup table, aggregate function, CTE

CTE

CTE stands for Common Table expressions. It was introduced with SQL Server 2005. It is a temporary result set and typically it may be a result of complex sub-query. Unlike temporary table its life is limited to the current query. It is defined by using WITH statement. CTE improves readability and ease in maintenance of complex queries and sub-queries. Always begin CTE with semicolon.

A sub query without CTE is given below :

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

By using CTE above query can be re-written as follows :

;With CTE1(Address, Name, Age)--Column names for CTE, which are optional
AS
(
SELECT Addr.Address, Emp.Name, Emp.Age from Address Addr
INNER JOIN EMP Emp ON Emp.EID = Addr.EID
)
SELECT * FROM CTE1 --Using CTE 
WHERE CTE1.Age > 50
ORDER BY CTE1.NAME



When to use CTE


This is used to store result of a complex sub query for further use.


This is also used to create a recursive query.



Table Variable

This acts like a variable and exists for a particular batch of query execution. It gets dropped once it comes out of batch. This is also created in the Tempdb database but not the memory. This also allows you to create primary key, identity at the time of Table variable declaration but not non-clustered index.

 GO
 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
 GO
 Select * from @TProduct --gives error in next batch
 



Note


Temp Tables are physically created in the Tempdb database. These tables act as the normal table and also can have constraints, index like normal tables.


CTE is a named temporary result set which is used to manipulate the complex sub-queries data. This exists for the scope of statement. This is created in memory rather than Tempdb database. You cannot create any index on CTE.


Table Variable acts like a variable and exists for a particular batch of query execution. It gets dropped once it comes out of batch. This is also created in the Tempdb database but not the memory.

--------------------------
create table tbl_test (iid int identity(1,1),strname varchar(300), iValue int )



insert into tbl_test(strname,ivalue) values('E',12)


select strname,SUM(ivalue) value into #temp from tbl_test group by strname  with cube
declare @iper int
select @iper=value from #temp where strname is null
select strname,iValue,sum(Convert(decimal(9,2),(Convert(decimal(9,2) , iValue)/Convert(decimal(9,2),@iper))*100)) 'percentage' into #temp1 from tbl_test
group by strname,iValue


;WITH cte AS
(
SELECT strname name,iValue,percentage,percentage score,ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS row
FROM #temp1
)

select bb.strname,iValue,aa.percentage,aa.score from
(SELECT a.name,a.percentage,SUM(b.score) score
FROM cte a
INNER JOIN cte b ON a.row >=b.row
GROUP BY a.name,a.percentage)aa inner join #temp1 bb on aa.name=bb.strname