Home > SQL > Nested Common Table Expression(CTE) in SQL Server 2005/2008

Nested Common Table Expression(CTE) in SQL Server 2005/2008


What is Common Table Expression (CTE)?

A common table expression (CTE) is a temporary storage result set, which will be accessible within the next execution scope of a query. That means we didn’t get CTE result after the second query statement.  A CTE is similar to a 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 very simple example of Common Table Expression (CTE) in SQL Server for pagination

Suppose we have a table ‘Employee’ with following structure. Suppose we need to apply pagination for this table, that means fetch only some part of data within the given index.  To achieve this, we usually insert all records into a temporary table to get a new id column as row number. Then we have to fetch data from this table as per this new column.

Table Structure:

By using Common Table Expression (CTE) we can accomplish pagination query with single statement as follows. In our CTE we are fetching data with row number, and from this CTE we are fetching some range of data as we needed. This query helps to get pagination data in SQL Server using CTE with single execution of query. The scope of the CTE will be ended just after this execution of statement.

WITH CTE AS
(
SELECT id
,name
,age
,joindate
,ROW_NUMBER() OVER (ORDER BY id DESC) AS RowNumber
FROM employee
)
SELECT *
FROM CTE
WHERE RowNumber BETWEEN 1 AND 5;

OUTPUT:

 

Is it possible Nested Common Table Expression (CTE) in SQL Server?

In some scenario we need to do some calculation from the result of first CTE and have to generate another result from the first CTE. For doing that, can we do CTE inside another CTE?  To accomplish this we can try with nested CTE in SQL Server which means declare a new CTE just after the first CTE to use result of first CTE.  Suppose we need to get number of years of experience of each employee. If we have very large number of data in the table, it will be very slow if we calculate this much of employee’s service years (If we have to fetch only 5 at a time).

In this case what is the better method is, first of all fetch the 5 records (pagination size) from the table, and apply the all calculation that we need to do for these 5 records only. We can accomplish this technique using CTE. What we are going to do is, first fetching 5 records from the table using CTE1, and then the very next step we are creating next CTE2. In CTE2 we are doing all calculation which we needed with result of CTE1.  Then it will be a faster pagination in SQL Server even having any complex calculation in the query. This nested CTE mechanism can be applied for anywhere in the SQL Server if we have any more complex calculation have to be done from the previous results. We can n number of CTE in nested CTE SQL Server 2005/2008. The last CTE can use previous CTE result, which can be used just previous CTE result and so on.

;with CTE1 as (
SELECT id
,name
,age
,joindate
,ROW_NUMBER() OVER (ORDER BY id DESC) AS RowNumber
FROM employee    
)
,CTE2 AS (
SELECT CTE1.id
,CTE1.name
,CTE1.age
,CTE1.joindate
,CTE1.RowNumber
,DATEDIFF(YEAR,CTE1.joindate,GETDATE()) as yearOfservce
FROM employee INNER JOIN CTE1
ON employee.id = CTE1.id
WHERE CTE1.RowNumber between 1 and 5
)
SELECT * FROM CTE2;

OUTPUT:

 

Why need to use Common Table Expression (CTE) for pagination query?

In some scenario there is some complex calculation have to do for the fetching queries. In such case if we do this calculation for all data in the table and fetch only paginated rows finally, there is some performance issue will be there and we didn’t get proper useful for using pagination. For this scenario we have to fetch paginated row from the table and do the complex calculation to those paginated rows only, it will improve the performance of pagination query. We can achieve it same as above queries using CTE (Common Table Expression) in SQL Server 2055/2008 for pagination. Common Table Expression (CTE) improve the performance of the pagination query in SQL Server as it takes only needed data first then do the calculation as above query.

About these ads
  1. David W. Wood
    June 13, 2012 at 11:19 pm | #1

    Perfect! Just what I’m trying to do.

  2. June 30, 2012 at 7:26 pm | #2

    Thanks David,
    Please keep visiting my blog :)

  3. Mike
    October 19, 2012 at 4:13 am | #3

    Seems you’ve missed the first line in your second example – it should say

    ;with CTE1 as (

  4. October 19, 2012 at 9:54 am | #4

    Hi Mike,

    You are correct, I missed that. I will edit the post today itself.

    THANK YOU for your pointing me the mistake.

  5. Chris
    December 6, 2012 at 2:50 am | #5

    Perfect

  6. December 6, 2012 at 7:46 am | #6

    Hi Chris,

    Thank you for your comment

  7. Pradeep
    March 6, 2013 at 6:08 pm | #7

    thanks.

  8. Frank
    June 18, 2013 at 11:48 pm | #8

    Good topic, but please have someone proofread your blog before you post it, preferably someone who speaks English as their first language. Thanks

  9. June 19, 2013 at 12:29 pm | #9

    Hi Frank
    Thank you for valuable comments. I will try to better more next post onwords..

  10. Liz
    March 26, 2014 at 5:39 pm | #10

    Just what I was looking for. Thanks!

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 42 other followers

%d bloggers like this: