Warning: is_writable() [function.is-writable]: open_basedir restriction in effect. File(/f5/sqllion/public/wp-content/uploads/shareaholic/) is not within the allowed path(s): (/fs3d/sqllion/:/nfsn/apps/php53/lib/php/:/nfsn/apps/php5/lib/php/:/nfsn/apps/php/lib/php/) in /fs3d/sqllion/public/wp-content/plugins/sexybookmarks/sexy-bookmarks.php on line 335
Common Table Expressions (CTE) | SQL Lion
Home > T - SQL > Common Table Expressions (CTE)

Common Table Expressions (CTE)

CTEs are one of the beautiful and the most powerful feature of SQL Server. It is introduced in SQL Server 2005 and I guess is one of the best things in SQL Server. This not only simplifies most of the complex and impossible queries in T-SQL, but also provides one medium to deal with recursive queries. Moreover, I can say it is mainly for recursive queries. It can be used in replacement of derived tables (sub queries), temp tables, table variables, inline user-defined functions etc.

The Common Table Expression (CTE) is called as a temporary named RESULT SET, within the scope of an executing statement that can be used within a SELECT, INSERT, UPDATE, or DELETE or even in a CREATE VIEW or MERGE statement.





CTE Syntax:

[ WITH <common_table_expression_name>]

[ ( column_name [ ,n ] ) ]

AS

( CTE_query_definition ),…n

  • < common_table_expression_name> : name of the CTE, used as a identifier. This should be different from any other CTE defined within the same “WITH” clause. The expression name can be same as the name of the base table or view (any reference to the expression name will use the CTE and not the base table).
  • [ ( column_name [ ,n ] ) ]: Represents the column names for the defined CTE (Optional if there is no duplicity in column names in the result set of the CTE_query_definition). And if the column names are defined with the CTE, then the number of columns should match with the inner result set’s columns.
  • ( CTE_query_definition ): This specifies a SELECT statement whose result set populates the common table expression. A CTE cannot define another CTE inside it and the SELECT statement must meet the same criteria as for creating a view.

More than one CTEs can be created within one “WITH” statement, but scope of all those CTEs will be limited to the immediate t-sql statement after “WITH” (described below in detail).

Stored procedures are mainly used to deal with complex logics either for security purpose or to accomplish some combined tasks, Or in most of the cases we use temporary tables or table variables to keep intermediate results which at the end tempt to be deleted when not in use. But among all these, CTE is the best as it act as a normal view only.

Simple CTE Example:  To get the employee information from the ‘employee’ table.

WITH emp_CTE as (
		select EmpName, empEmailId, salary 
		from dbo.employee)
 select * from emp_CTE;

OUTPUT:

CTE1

This is just to show how CTEs are working. Let’s take another example, where we want information from employee along with departmental information and then want to do some operations on it.

WITH emp_CTE as (
		select EmpName, empEmailId, salary, DeptName, DeptStatus
		from dbo.employee A
		inner join 
		dbo.Department D on A.DeptID = D.DeptID )
 
 select * from emp_CTE where DeptStatus = 'Active';

OUTPUT:

CTE2

This is yet another simple query, where one CTE named “emp_CTE” is defined to keep information about employees along with departmental information.

Some more CTEs examples:

WITH   CTE1(CL11,CL22)	 	as	(select 1 as CL1, 3 as CL2)
	,CTE2(CL3,cl5,cl6)	as	(select 1,5,7)
 
select * from CTE1 inner join CTE2 on CL11 = CL3

OUTPUT:

CTE3

Here we defined two CTEs (i.e. CTE1 and CTE2) within the same “WITH” statement, and both these CTEs are used in the immediate t-sql statement.

CTEs with Union ALL

WITH CTE_union(CL1,CL2)	AS	(
		SELECT 1 AS CL1, 3 AS CL2
		UNION ALL 
		SELECT 5 as CL11,7 as CL22)
 
select * from CTE_union

OUTPUT:

CTE4

CTEs are very useful for on the fly IDENTITY columns:

WITH emp_CTE as (
	select ROW_NUMBER() over(order by (select 1)) as IdentityId, *
	from dbo.employee )
select * from emp_CTE

OUTPUT:

CTE5

SQL Server supports two types of Common Table Expressions (CTEs):

  • Recursive CTEs:  Here the query executes itself, like we use recursive functions (functions calling itself) in C, java or C# .net programming languages.  This is very useful in case of parent-child hierarchy relationship or base-sub class or category-subcategory, etc.
  • Non- Recursive CTEs: These are the other complex queries that can be simplified with the help of CTEs like using cursors, getting sequence numbers, filtrations, etc.

Recursive CTEs: The Recursive CTEs must have two types of query definition members:

  • Recursive Member: If the query definition references the CTE itself, then it is called Recursive member. This is defined at the last i.e. after defining all the anchor members.
  • Anchor Member: All other CTE query definitions without reference of CTE are Anchor Members. All anchor member query definitions must be put before the first recursive member definition.




The following are the restrictions to Recursive CTEs:

  • In Recursive CTEs, it’s not possible to have multiple query definitions (i.e. multiple CTEs [, separated]) within a single “WITH” statement.
  • All anchor members supports UNION ALL, UNION, INTERSECT, or EXCEPT for combining the anchor definition. But only UNION ALL is supported for combining the last anchor member and the first recursive member and even for multiple recursive members.
  • The total number of columns and the data types of those columns must match for the anchor member and the recursive member. (Note: data type should be same for recursive member and the corresponding anchor member)
  • CTE expression_name can be referenced only once in the FROM clause of a recursive member.

Recursive Common Table Expression Examples: These are very useful in case of hierarchical data, because the CTE Query continues to execute till it reaches its lower granular level.

The below example is based on the “AdventureWorksDW2008R2” sample database.

WITH EmpDetails (EmployeeKey, FirstName, LastName, ParentEmployeeKey, PFirstName, PLastName,  EmployeeRank)
  AS
  (
    SELECT 
		 EmployeeKey
		,FirstName
		,LastName
		,ParentEmployeeKey
		,cast('NULL' as varchar(50)) as PFirstName
		,cast('NULL' as varchar(50)) as PLastName
		,1 as EmployeeRank
    FROM dbo.DimEmployee
		WHERE ParentEmployeeKey IS NULL
    UNION ALL
    SELECT 
		 e.EmployeeKey
		,e.FirstName
		,e.LastName
		,e.ParentEmployeeKey
		,cast(CTE_Emp.FirstName as varchar(50)) as PFirstName
		,cast(CTE_Emp.LastName as varchar(50)) as PLastName
		,CTE_Emp.EmployeeRank + 1 as EmployeeRank
    FROM dbo.DimEmployee e
      INNER JOIN EmpDetails CTE_Emp
        ON e.ParentEmployeeKey = CTE_Emp.EmployeeKey
  )
 
select * from EmpDetails order by EmployeeRank

OUTPUT:

CTE6

Let’s dissect the above query to have a better picture of its working mechanism.

CTE7

Basically any recursive CTE consists of three elements:

  1. First invocation of the CTE: In first invocation, all the members of the CTE will be executed i.e. both the anchor members and the recursive members. And as we have CTE referenced in the recursive members, it will automatically invoke the CTE for further iterations and thus called as recursive mode or recursive invocation. In this case Anchor member’s result set will be considered as the referenced data for the CTE. As in the above figure, in the first invocation, r0 is the value for the Anchor query and hence become the data for the CTE when referenced from the Recursive query.
  2. Recursive invocation of the CTE:  In all recursive invocation of the CTE, only recursive members will be executed and the anchor members will remain idle. And the data for the CTE referenced will be the result set built from the last call of the CTE. Elaborated briefly below.
  3. Recursive Termination Condition: Well practically, we don’t have any control on the termination of the Recursive CTEs. I mean it is implicit; it will stop itself when no rows are returned from the previous invocation. Hence, the control condition can be applied indirectly i.e. the query’s result set can be limited with the help of filter conditions.

Let’s look around the execution process of the above recursive CTE.

First Round: As in the above picture, r0 is the result set of the Anchor query. And when coming to Recursive query, r0 become the CTE value when referenced and joined with the Employee table E.

Anchor Resultset:  r0 [Top level employee with id 112]

CTE8

Recursive Resultset (r1): E inner join r0 [based on joining condition E.ParentEmployeeKey = r0.EmployeeKey] i.e. all 2nd level employees

CTE9

Second Round: From second round, the Anchor query will remain idle. Only the recursive queries will be executed. But the referenced CTE value will be the value of the last invocation only (r1) and not the union of [(r0) and (r1)]

Anchor Resultset: No result set (query remain idle)

Recursive Resultset (r2): E inner join r1 [based on joining condition E.ParentEmployeeKey = r1.EmployeeKey] i.e. all 3rd level employees

CTE10

and so on till (rn), till it reaches all the levels i.e. when there will be no records from the previous invocation of the CTE.

After execution of all the iterations, the final result set will be accumulated by merging all partial result sets by UNION ALL i.e. [(r0) + (r1) + (r2) + … + (rn)]

CTE11

If the conditions applied are not valid inside CTE query definition i.e. if the CTE is queried incorrectly, it may enter to an infinite loop.  For example, in the above example, instead of “ON e.ParentEmployeeKey = CTE_Emp.EmployeeKey” if we apply “ON e.EmployeeKey= CTE_Emp.EmployeeKey”; it will enter an infinite loop.  May be the system will crash out of memory (kidding :) ). Once it extends beyond the number of recursion permitted (by default it is 100), it will show an error like the below:

Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

In order to avoid this type of error we can use MAXRECURSION hint in the OPTION clause of the outer query where the CTE is referenced. We can limit the number of recursion levels allowed for a particular statement by using the MAXRECURSION hint and a value between 0 and 32,767. Like for example, in the above problem, if we want to see the result set till the employee Rank 3 (employee level), we can use the MAXRECURSION hint to ‘2’ (two sub level + 1 root level) as below:

WITH EmpDetails (EmployeeKey, FirstName, LastName, ParentEmployeeKey, PFirstName, PLastName,  EmployeeRank)
  AS
  (
    SELECT 
		EmployeeKey
		,FirstName
		,LastName
		,ParentEmployeeKey
		,cast('NULL' as varchar(50)) as PFirstName
		,cast('NULL' as varchar(50)) as PLastName
		,1 as EmployeeRank
    FROM dbo.DimEmployee
		WHERE ParentEmployeeKey IS NULL
    UNION ALL
    SELECT 
		e.EmployeeKey
		,e.FirstName
		,e.LastName
		,e.ParentEmployeeKey
		,cast(CTE_Emp.FirstName as varchar(50)) as PFirstName
		,cast(CTE_Emp.LastName as varchar(50)) as PLastName
		,CTE_Emp.EmployeeRank + 1 as EmployeeRank
    FROM dbo.DimEmployee e
      INNER JOIN EmpDetails CTE_Emp
        ON e.ParentEmployeeKey = CTE_Emp.EmployeeKey
   )
 
select * from EmpDetails -- order by EmployeeRank
OPTION (MAXRECURSION 2)

But in this case we cannot use order by clause with hints.

Some more examples for recursive CTEs:

with echo_CTE(st)
as
(
select st = cast('Hi ' as varchar(50))
union all
select cast(st + 'Arun! ' as varchar(50)) from echo_CTE
)
select st from echo_CTE 
OPTION (MAXRECURSION 3)

CTE12

I mentioned CAST operator in the above example, because recursive CTEs are data type sensitive as well as data size sensitive. So, be careful, while selecting the columns for the CTE.

Here the above statement is controlled by “OPTION (MAXRECURSION 3)” (to run only three iteration) but it can be also controlled by using filter condition as below (I modified the above query to give more clarity to the solution).

with echo_CTE(st, lencount)
as
(
select st = cast('Hi ' as varchar(50)), LEN('Hi ')
union all
select 
	cast(st + 'Arun! ' as varchar(50))
	, len(st) from echo_CTE
where lencount < 50
)
select st, lencount from echo_CTE

CTE13

Note in the above output, the Lencount column is always calculating for the value of st from the previous iteration.

Now let’s try one complex operation with CTE, i.e. to split comma separated values (CSV) into table set.

Declare @stringst varchar(8000) = 'Sign UP, today, and, start up ,with, new, ideas and, discussions, in, SQL Lion, Forums'
SET @stringst = @stringst + ','; --';' is needed before starting CTE defination
 
With SplitString(startid, endid) AS
(
	select 0 as startid, CHARINDEX(',',@stringst,0) as endid
	UNION ALL
	select endid + 1 as startid, CHARINDEX(',',@stringst, endid + 1) as endid from SplitString
	where endid < LEN(@stringst)
)
 
select SUBSTRING(@stringst,startid,endid - startid) as SplitedValue, *, (endid - startid) as wordSize from SplitString

CTE14

Non-Recursive Common Table Expressions: These are CTEs with only Anchor Members and no Recursive members. Even CTEs in non-recursive mode are more powerful. Let me show one example.

To delete redundant records from a table without using any identity column or any other third table.

WITH CTE as
(
select
ROW_NUMBER() over(partition by col1, col2, col3 order by col1) as RecID,
* from dbo.ABC
)
 
DELETE from CTE where RecID > 1

And the coolest part of this is that it will not hamper the structure of the table i.e. all constraints and defaults will remain same as it is.

Well, this is not the end of the story! The CTE has enormous capabilities to take over any complex situation. Check out more in my upcoming posts. Register or sign-up for the latest updates.

Categories: T - SQL
  1. jacobtile
    August 16th, 2010 at 22:16 | #1

    Hi,
    Thank you for this nice explanation for CTES, your blog is a very good source of information for sql server professionals.
    I always face problems while implementing any problem with CTE.
    This article if very useful.
    Thanks once again.
    -Jacob

  2. kevin
    August 17th, 2010 at 21:49 | #2

    Thanks for the nice article.
    Is it possible to use CTEs inside Views for update operation.

    waiting for your reply.
    Thanks

  3. nailgun
    August 20th, 2010 at 02:12 | #3

    Best article I’ve read on CTEs and recursion.

    Thank you

  4. fg
    August 23rd, 2010 at 05:34 | #4

    Great site. A lot of useful information here. I’m sending it to some friends!

  5. forex robot
    August 24th, 2010 at 00:10 | #5

    Wow this is a great resource.. I’m enjoying it.. good article

  6. pa
    August 25th, 2010 at 07:44 | #6

    Pretty nice post. I just stumbled upon your blog and wanted to say that I have really enjoyed reading this post.

  7. Jason Orlando
    September 4th, 2010 at 00:54 | #7

    This post is very useful, and i’m attempting attempting to use the power of CTE’s in some of my applications. One problem i’ve run into is that i cannot use a correlated query involving a CTE where the CTE is a nested inside another query.

    Maybe this is possible, but i’m not seeing a way to do it in one single query.

    Can you try using your example tables above to solve the following challenge:

    Show me a table with the following column output:
    EmployeeKey, FirstName, LastName, ReportingHierarchy, (all other DimEmployee Fields)

    where ReportingHierarchy uses a CTE to recursively climb the employee/manager relationships and construct a string with the following style output:

    Employee1 –> Employee2 –> Employee3 –> … –> EmployeeN (always the CEO)

    this string would climb all the way to to the top of the org hierarchy for each employee, so you can see the chain of command above that employee (up to the CEO for example)

    For employees 1 level below the CEO, the string would be be like:
    ‘Orlando, Jason -> Smith, Jack (CEO)’

    for employees 2 levels below the CEO the string would look something like:

    ‘Orlando, Jason -> French, Steve -> Smith, Jack (CEO)’

    The problem I’m having is finding some way of getting the CTE data to co-exist in the same table with the rest of the DimEmployee fields, using a single query, and NOT having to group by all of the DimEmployee fields in the statement which selects from my CTE.

    The crux of the problem is that you need to normally do a MAX(rownumber) at the end of the recursive CTE to show only the last iteration of the recursion, but when I use the MAX function, i’m then forced to do a group by at the end and this makes including an arbitrary amount of columns of output afterwards far more difficult/irritating.

  8. lexus is
    September 6th, 2010 at 03:17 | #8

    Amazing post thank you!

  9. Pramod
    September 14th, 2010 at 11:59 | #9

    Excellent article.
    Though heard many times about CTE but understood CTE concept and its actual usage only after reading the post.

    Especially the employee hierarchy query was much useful for me.

    Thanks.

  10. sudheer
    October 14th, 2010 at 12:13 | #10

    Hi Arun,

    It’s nice to read ur articles and bring into play in my project. I am looking for Normalization article from you could you please post this to email id : sudhir2192006@gmail.com . I googled a lot but I couldn’t get positive explanation.

    Advanced Thanks
    G. Sudheer Kumar

  11. Jakob
    October 20th, 2010 at 02:18 | #11

    @Jason Orlando
    Jason, try creating your CTE with only the ID for the employee and the string, then create your select statement or another CTE joining your arbitrary number of columns on the Employee ID.

    I use CTEs with aggregation frequently so that I can operate on the aggregate value without an overlong group by.

  12. October 24th, 2010 at 15:20 | #12

    Hi just thought i would tell you something.. This is twice now i?ve landed on your blog in the last 3 weeks looking for completely unrelated things. Great Info! Keep up the good work.

  13. Neeraj
    October 29th, 2010 at 14:10 | #13

    @Jason Orlando
    This may solve your problem:
    Table EMP
    Columns: EMPID, NAME, MGRID

    WITH CTE

    AS

    (

    SELECT EMPID , [NAME] , CAST ( ” AS VARCHAR ( 50 )) AS MANAGER , 1 [LEVEL] , CAST ( [NAME] AS VARCHAR ( 50 )) HIERARCHY

    FROM EMP WHERE MGRID IS NULL

    UNION ALL

    SELECT EMP . EMPID , EMP . [NAME] , CAST ( CTE . NAME AS VARCHAR ( 50 )) AS MANAGER , CTE . LEVEL + 1 , CAST ( CTE . HIERARCHY + ‘->’ + EMP . [NAME] AS VARCHAR ( 50 )) HIERARCHY

    FROM

    EMP INNER JOIN CTE

    ON EMP . MGRID = CTE . EMPID

    )

    SELECT * FROM CTE

  14. Gilmar Maia
    November 23rd, 2010 at 02:19 | #14

    Parabéns Arun,

    Seu artigo foi muito útil. Está completo e bastante didático. Já está no meu Favoritos.

    Abraços,

    ————————–
    Portuguese to English translation
    Congratulations Arun,

    Your article was very helpful. Is complete and quite instructive. Already in my favorites.

    Hugs,

  15. December 21st, 2010 at 09:01 | #15

    Hi everyone,

    I wonder if the CTE technique could help with this challenge.

    I have a long list of Call Detail Records.
    We have the StartDateTime and EndDateTime for each call.
    Some of the calls have overlapping in time.
    I would like to generate an extra column for each record that contains a Group ID that is unique to each group of overlapping calls. It is possible to have 5 or 10 calls overlapping.

    When we have this result set, we will partition on this new column to figure out counts, max duration, and other aggregate functions.

    What do you think? Is CTE the technique we need for this application?

    Thanks!

    Jim

  16. December 21st, 2010 at 09:03 | #16

    And, can you help me write it? :-)

  17. December 24th, 2010 at 12:52 | #17

    Hi,
    if you can send me some sample data and the output you wanted then, possibly i may help on it.

    CALLid Startdatetime Enddatetime
    101 1/2/10 10:00 AM 1/2/10 10:30 AM
    102 1/2/10 10:20 AM 1/2/10 10:40 AM
    103 1/2/10 10:00 AM 1/2/10 10:30 AM

    –Arun

  18. February 14th, 2011 at 22:45 | #18

    I plan to use this technique to solve the very common problem of displaying a Bill of Material in a manufacturing environment (the list of components used to make a finished product – typically made of a heirarchy (tree structure) of subassemblies and raw materials or purchased components). Normally people write programs to do this and I’ve always wanted to find a way to do it with SQL. I think/hope you have given me that tool. Thanks for the great post.

  19. Madhavi
    March 31st, 2011 at 03:49 | #19

    Nice Article Arun..You Rock !

  20. April 1st, 2011 at 13:21 | #20

    Nice article. Share my experience when I use CTE.
    I have a view. Just say the view named as view_employee. Inside the view, there’s a CTE statement. When I want to create index in the view, there’s an error. I try to find solution for the problem using google.com. Then, I find a site http://msdn.microsoft.com/en-us/library/ms191432.aspx . The site describes about view with indexing (Indexed View). The site says that there are requirements when you create index on the view. One of them is The SELECT statement in the view cannot contain A common table expression (CTE). I don’t want to eliminate the CTE in view_employee, and I need index in the view because performance issue. I don’t have any idea on these problems.
    Are there any solutions for these problems. thank you.

  21. May 26th, 2011 at 00:49 | #21

    One more way to split CSV values into rows:

    DECLARE @StagingID AS VARCHAR (30) = ’27,333,4,7022,5453,565,0,4,’;

    WITH UniqueStagingIDs (Number, endPt)
    AS (SELECT LEFT(@StagingID, CHARINDEX(‘,’, @StagingID) – 1) AS Number, CHARINDEX(‘,’, @StagingID) AS endPt
    UNION ALL
    SELECT SUBSTRING(@StagingID, endPt + 1, CHARINDEX(‘,’, @StagingID, endPt + 1) – (endPt + 1)), CHARINDEX(‘,’, @StagingID, endPt + 1) AS endid
    FROM UniqueStagingIDs
    WHERE endPt < LEN(@StagingID))
    SELECT *
    FROM UniqueStagingIDs;

  22. Darshan
    June 9th, 2011 at 12:53 | #22

    Great art

  23. Darshan
    June 9th, 2011 at 14:47 | #23

    article

  24. Poli Reddy
    June 17th, 2011 at 11:57 | #24

    Hi Arun,

    In JSP / HTML, I want to restrict the size of file being uploaded.

    If the size of file more than a specific size say 5MB, i want to give mesage to the user. I don’t want to wait till the file gets uploaded to server and then validate the file size. I need client side validation. Can it be done using scripting language or any other way?

    I need a solution for this in Internet Explorer. I got solution using ActiveX control:
    var myFSO = new ActiveXObject(“Scripting.FileSystemObject”);
    var filepath = document.getElementById(‘myfile’).value;
    var thefile = myFSO.getFile(filepath);
    var sizeInBytes = thefile.size;

    But ActiveX controls are not allowed in our application. Is there any alternative way for this?

    PLease suggest the possible ways.. Thanks in advance!!

  25. swati
    July 4th, 2011 at 18:34 | #25

    thanx fr the nice explanation..with each and every details about CTE

  26. Satish
    August 5th, 2011 at 14:59 | #26

    Hi ,
    Thanks for your nice explanation. it helps me a lot

  27. Gobikrishnan
    September 29th, 2011 at 20:16 | #27

    Thank you so much for much more detailed explanation. I was confused and got irritated on this CTE. But, Now, i see the power of it.

  28. Muhammd Faheem
    September 29th, 2011 at 20:28 | #28

    Thanks.
    Its really a very good explanation of CTE.
    Very goood…

  29. Prem
    October 10th, 2011 at 22:46 | #29

    I like your style of explaining. You go gradually from simple to complicated. And you cover evrey thing. Keep up the good work.

  30. TesmerR
    October 13th, 2011 at 17:20 | #30

    thanks good article, especially the CTE method for deleting obsolete/duplicated rows

  31. fghfg
    October 14th, 2011 at 14:30 | #31
  32. mini ugg boots
    December 1st, 2011 at 19:20 | #32

    Its just like you read my mind! You manage to know so much related to this, really like you wrote the book in it or something. It is my opinion that you could do with some pics to drive your message home a little, but aside from that, surely stellar blog..

  33. telecom news
    December 4th, 2011 at 19:51 | #33

    I relish, cause I discovered exactly what I used to be looking for. You’ve ended my 4 day lengthy hunt! God Bless you man. Have a nice day. Bye

  34. shabbir
    February 26th, 2012 at 00:23 | #34

    A very nice article on CTE

  35. August 15th, 2012 at 11:45 | #35

    Hi,
    Nice Articles.
    I try this …
    CREATE TABLE [dbo].[CSVTest](
    [ID] [int] NULL,
    [FirstName] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [LastName] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [BirthDate] [smalldatetime] NULL
    )

    select * from dbo.CSVTest

    with emp (ID,FirstName) AS
    (
    select ID,FirstName from CSVTEST
    )
    after running statement showing error,
    Msg 102, Level 15, State 1, Line 4
    Incorrect syntax near ‘)’.

    Please suggest me solution….

  36. August 16th, 2012 at 23:29 | #36

    “CTEs are one of the beautiful and the most powerful feature of SQL Server. It is introduced in SQL Server 2005 and I guess is one of the best things in SQL Server. ”

    First, I Agree 100%.

    I recently started using rCTE’s and can’t say enough for how they have helped my SQL development.

    You mentioned maxrecursion and gave a couple fabulous examples. One that I would like to include is (MAXRECURION 0). You lose your infinate loop protection but can use your rCTE for stuff that involves more than 32,767 rows. I use one for a running total that does 1,000,000 rows in under 20sec (with an index). A T-SQL cursor or loop (non-recursive) can’t do that!

  37. Anjum
    September 14th, 2012 at 17:52 | #37

    Thanks all for your nice explanation. it helps a lot.

  38. Dhruva
    February 1st, 2013 at 11:43 | #38

    @Dinesh Dattatray Vishe
    in the given example you haven’t use CTE which you have created using WITH keyword.
    please add the below line

    SELECT * FROM EMP;

  39. Dhruva
    February 1st, 2013 at 11:47 | #39

    Arum, I think this is an article that anyone can use to start working on CTE. It does not require any guidance as this is a self explanatory.

    Thank you for making such a great article.

  40. Dhruva
    February 1st, 2013 at 11:47 | #40

    Dhruva :Arun, I think this is an article that anyone can use to start working on CTE. It does not require any guidance as this is a self explanatory.
    Thank you for making such a great article.

  41. May 3rd, 2013 at 08:06 | #41

    Hey there, I think your blog might be having browser compatibility issues.

    When I look at your blog site in Ie, it looks fine but when opening in Internet Explorer,
    it has some overlapping. I just wanted to give you a quick heads up!

    Other then that, fantastic blog!

  42. June 2nd, 2013 at 18:05 | #42

    When I initially commented I clicked the “Notify me when new comments are added” checkbox and now each
    time a comment is added I get four emails with the same comment.
    Is there any way you can remove people from that service?

    Cheers!

  43. September 5th, 2013 at 02:52 | #43

    In fact, they were invented by the Russians as a primitive version of
    the dumbbell. The person that instructs Russian Kettlebell training is Pavel Tsatsouline.
    So how can we steadily transition to the Basic Workout
    to the Advanced Workout.

  44. September 5th, 2013 at 04:10 | #44

    What’s worse is that it is not a fashion disaster that can be solved with
    just a change of clothes. You could; however, be back to normal and return to work within a couple of days.
    It is best to drink lots of water – you need to have at least 6-8 glasses of water in a day while using a good daily skincare regimen which includes cleansing routines,
    toning sessions and also moisturizing routines.

  45. October 8th, 2013 at 06:19 | #45

    Do you mind if I quote a few of your posts as long as I provide credit and sources back to
    your blog? My blog site is in the very same area of interest as yours and my users would genuinely benefit from some of the information you
    present here. Please let me know if this alright with you.
    Thanks!

  1. May 16th, 2011 at 04:17 | #1
You must be logged in to post a comment.