June 17, 2014

Comman Table Expression and its usage

According to Microsoft:

A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE or CREATE VIEW statement. A CTE is similar to derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.

A CTE can be used to:
  • Create a recursive query.
  • Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata.
  • Enable grouping by a column that is derived from a scalar subselect or a function that is either not deterministic or has external access.
  • Reference the resulting table multiple times in the same statement.
Using a CTE offers the advantages of improved readability and ease in maintenance of complex queries. The query can be divided into separate, simple logical building blocks. These simple blocks can then be used to build more complex, interim CTEs until the final result set is generated.

CTEs can be defined in user-defined routines, such as functions, stored procedures, triggers, or views.

Now, I would like to show some examples so far I have experienced.

For Example:

;WITH tbl(Id, AvgValue)
       SELECT Id, Avg(Value) as AvgValue
       FROM Quantity
       GROUP by Id

SELECT Name, AvgValue
customer as cst on cst.id = tbl.id
Order by Name

In the above example, you can see we have used subquery in order to use in the main query. It's kind of virtual view.


Lets suppose there are 3 tables which holds information about Customer Name and their respective County. First table named Customer contains their basic info, second table County and third table named CustomerCounty has relationship between Customer and County table.

;WITH tblCTE (RowNumber, CustomerId, CountyName, County) AS
  SELECT 1, CC.CustomerId, MIN(C.CountyName), CAST(MIN(C.CountyName) AS VARCHAR(Max))
  FROM CustomerCounty as CC
  INNER JOIN County as C ON CC.CountyId = C.CountyId
  GROUP BY CustomerId


  SELECT CTE.RowNumber + 1, CC.CustomerId, C.CountyName, CTE.County + ', ' + C.CountyName
  FROM CustomerCounty as CC
  INNER JOIN County as C ON CC.CountyId = C.CountyId
  INNER JOIN tblCTE as CTE ON CTE.CustomerId = CC.CustomerId
  WHERE C.County > CTE.County

SELECT CTE.CustomerId, S.Name, Areas
INNER JOIN Customer as C ON C.CustomerId = CTE.CustomerId
INNER JOIN (SELECT CustomerId, MAX(RowNumber)
AS Max FROM CTE GROUP BY CustomerId) as tbl
ON CTE.RowNumber = tbl.Max AND CTE.SalesmanId = tbl.CustomerId
ORDER BY CustomerId

In the above example we have see Common Table Expression has two queries, first one acts as a base query and another acts as a recursive query. The second query is using CTE so this helps in recursive processing. The above example helps to concatenate string of different row into single one.


CustomerId Name Areas
1                 Harke       Luton, Bedford, Milton keynes
2                 Matt         Hitchin, Stevenage

No comments: