Common Table Expressions (CTE) :-

Common Tabel Expressions (CTE)

Common Table Expressions (CTEs) were one of the most exciting features to be introduced with SQL

Server 2005

A CTE is a "temporary result set" that exists only within the scope of a single SQL statement. It

allows access to functionality within that single SQL statement that was previously only available

through use of functions, temp tables, cursors, and so on...

CTE basics:-

The concept behind a CTE is simplicity itself. Consider the following statement:

with MyCTE(x)

as

(select x='hello')

select x from MyCTE

This defines a CTE called MyCTE. In brackets after the as keyword is the query that defines the CTE.

The subsequent query references our CTE, in this case simply returning the string "hello".

Like a derived table, a CTE lasts only for the duration of a query but, in contrast to a derived

table, a CTE can be referenced multiple times in the same query. So, we now we have a way of

calculating percentages and performing arithmetic using aggregates without repeating queries or using

a temp table:

with MyCTE(x)

as

(select top 10 x = id from sysobjects)

select x, maxx = (select max(x) from MyCTE), pct =100.0 * x / (select sum(x) from MyCTE)

from MyCTE

This returns (on my system):

	x           maxx         pct 

	4           2137058649   2.515723270440

	5           2137058649   3.144654088050

	7           2137058649   4.402515723270

	8           2137058649   5.031446540880

	13          2137058649   8.176100628930

	15          2137058649   9.433962264150

	25          2137058649   15.723270440251

	26          2137058649   16.352201257861

	27          2137058649   16.981132075471

	29          2137058649   18.238993710691

CTE and recursion:-

More interesting, in my opinion, is the use of recursion with CTEs. The table defined in the CTE

can be referenced in the CTE itself to give a recursive expression, using union all:

with MyCTE(x)

as
(

select x = convert(varchar(8000),'hello')

union all

select x + 'a' from MyCTE where len(x) < 100

)

select x from MyCTE

order by x

The query:

select x = convert(varchar(8000),'hello')

is called the anchor member. This is executed in the first pass and will populate the CTE with the

result, in this case hello. This initial CTE is repeatedly executed until the complete result set is

returned. The next entry:

select x + 'a' from MyCTE where len(x) < 100

is a recursive member as it references the CTE, MyCTE. The recursive member is executed with the

anchor member output to give helloa. The next pass takes helloa as input and returns helloaa, and so

on so that we arrive at a CTE populated with rows as follows:

hello

helloa

helloaa

helloaaa

helloaaaa

….........