No more temporary Table Use CTE

Avoid using temporary tables and derived tables as it uses more disks I/O. Instead use CTE (Common Table Expression); its scope is limited to the next statement in SQL query.

The following example shows the components of the CTE structure: expression name, column list, and query. The CTE expression Sales_CTE has three columns (SalesPersonID, SalesOrderID, and OrderDate) and is defined as the total number of sales orders per year for each salesperson.

CTE WITH SINGLE USE

USE ARTEK;

GO

-- DEFINE THE CTE EXPRESSION NAME AND COLUMN LIST.

WITH SALES_CTE (SALESPERSONID, SALESORDERID, SALESYEAR)

AS

-- DEFINE THE CTE QUERY.

(

    SELECT SALESPERSONID, SALESORDERID, YEAR(ORDERDATE) AS SALESYEAR

    FROM SALES.SALESORDERHEADER

    WHERE SALESPERSONID IS NOT NULL

)

-- DEFINE THE OUTER QUERY REFERENCING THE CTE NAME.

SELECT SALESPERSONID, COUNT(SALESORDERID) AS TOTALSALES, SALESYEAR

FROM SALES_CTE

GROUP BY SALESYEAR, SALESPERSONID

ORDER BY SALESPERSONID, SALESYEAR;

GO

CTE WITH MULTIPLE USE

WITH CT (CITYID,STATEID,CITYNAME) AS

(

SELECT CITYID,STATEID,CITYNAME

FROM CITY

)

,

ST (STATEID,STATENAME, REGIONID) AS

(

SELECT STATEID,STATENAME, REGIONID FROM STATE

)

,

RT (REGIONID,REGIONNAME) AS

(

SELECT RR.REGIONID,RR.REGIONNAME FROM REGION RR

JOIN ST ON ST.REGIONID = RR.REGIONID

)

SELECT CT.CITYNAME, ST.STATENAME, RT.REGIONNAME

FROM CT

JOIN ST ON CT.STATEID = ST.STATEID

JOIN RT ON RT.REGIONID = RT.REGIONID

IN ABOVE QUERY THE ST CTE IS BEING USED TWICE.

Posted by: MR. JOYDEEP DAS