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.
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;
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;
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.