02 August 2006

SQL Server 2005 Common Table Expressions

Common Table Expressions (CTEs) are one of the most exciting features to be introduced with SQL Server 2005. Nigel Rivett explains what they are and how they can be used.

In my opinion, Common Table Expressions (CTEs) are 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:

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:

This returns (on my system):

Note that although this has only referenced sysobjects once in the CTE, the query plan will confirm that sysobjects is actually scanned 3 times – each aggregate in the result set causes an additional scan. As a result, it would still be more efficient to accumulate the values in a temporary table or table variable if you are accessing large tables.

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:

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:

The recursion will terminate when the recursive member produces no rows – in this case recursion stops when the length of x equals 99 – or when the recursion limit is reached (more about that later). The CTE is then output by the following statement:

select x from MyCTE
order by
x

There are a few interesting issues associated with even this simple CTE usage. Note that the anchor member populates the CTE with a varchar(8000). Had the convert not been included then you would expect the datatype of x to be defined by that of the anchor member (varchar(5)) and to give an error when trying to insert the longer recursive entries. This does indeed happen – but you would expect varchar(1000) to be fine. Not so. This still produces the error that the datatypes don’t match. In fact, you should always cast the recursive member output to be the same as the anchor member:

But why, then, did the code work with varchar(8000)? This looks like a bug. As far as I can tell varchar(8000) and nvarchar(4000) seem to be the only values that work. Varchar(max) and varchar(7999) give an error as do all the other values that I have tried.

Multiple anchor members

Any entry that does not reference the CTE will be considered an anchor member so we can also include multiple anchor members using a union all:

This adds two rows from the anchor members and hence the recursive member acts on two rows for each pass to produce the output:

The first recursive pass produces the output “helloa” and “goodbyea”. The second produces “helloaa” and “goodbyeaa”. The third produces “helloaaa” and “goodbyeaaa”. For subsequent passes the string containing “goodbye” is too long for the check len(x)<10 but recursion continues as long as some output is produced – so we get more entries from hello than goodbye.

Multiple recursive members

We can also include multiple recursive members to produce extra rows at each pass:

This produces the result:

In order to understand this output, you need to remember that the input to each pass is the output from the previous pass. On the first recursive pass the input is the row from the anchor member. This produces two rows – one from each recursive member. On the next pass the input is the two rows output from the previous pass. Each recursive member outputs two rows producing four in total. In other words, the number of rows output doubles with each pass.

We can modify the output by limiting the rows on which the recursive members act. For example:

giving:

Another method is to flag the recursive members:

This gives the same result as above. It forces each recursive member to act only on the anchor member output and on any output that it itself has produced. This is at the expense of including the redundant data column “i” in the CTE but it does make the code a lot more readable. Using a similar technique we can output a value to indicate which pass produces each row:

This returns the following, r1 giving the pass number for the first recursive member and r2 for the second:

This is very useful for debugging and also for detecting how a CTE is processed and for controlling the number of passes for each recursive member. For instance:

Here I have terminated the first recursive member after two iterations and the second after five, giving:

Note that recursion continues until a pass produces no output – although the first recursive member terminates early recursion continues until both recursive members produce no output.

Recursion limit

In order to demonstrate the recursion limit, we start by producing a series of numbers in a CTE:

This happily produces the numbers 1 to 100 (a tally table). However, try increasing the recursion limit as follows:

You will receive the following error:

The statement terminated. The maximum recursion 100 has been exhausted before statement completion

It sounds like there is a limit of 100 for recursion but luckily this is just the default limit. For development this is very useful; to save time and space consider setting it lower for first attempts.

The maximum number of recursions can be set via the maxrecursion option, up to a maximum of 32767 (wouldn’t it be nice, though, if the error message suggested that the recursion limit should be increased?):

Uses for Common Table Expressions

Finally, let’s review some of the more interesting applications of CTEs.

Traversing a hierarchy

This is covered well in BOL and now gives SQL Server something to compete with the Oracle connect by operator.

Date Ranges

A common requirement is to aggregate entries per day (or month or year). This is easy using a group by, if there are entries for every day:

giving:

However, if there are some days with no transactions the result set, rather than report zero, does not give an entry for that day. To get round this we need to left join to a tally table which includes the days on which we wish to report. With a CTE this becomes simple. For example, for a year:

Note the “;” before the CTE definition – that’s just a syntax requirement if the CTE declaration is not the first statement in a batch.

Parsing CSV values

It is common to pass a CSV string in to a stored procedure as a means of passing in an array of values. Often this is turned into a table via a function. Using a CTE we can now contain this code within the stored procedure:

The output is as follows:

How does this work? The anchor member, select i=1, j=charindex(‘,’,@s+’,’), returns 1 and the location of the first comma. The recursive member gives the location of the first character after the comma and the location of the next comma (we append a comma to the string to get the last entry). The result set is then obtained by using these values in a substring.

In the previous example the CTE output was the start and end locations of each of the strings. We can instead produce the strings themselves; the CTE code becomes a little more complicated but the following query is simplified:

Beyond 32767

What happens if you want a list of numbers that extends beyond 32767? Although the recursion limit is 32767 it is possible to create extra entries. For example, the following CTE returns all the numbers from 0 to 64000. The result set produced is used to check the results. The maximum and count verify that all of the numbers are present, assuming that the result is a sequence:

To take this a step further we can accumulate values by manipulating the CTE:

giving:

In other words, this returns all the numbers from 0 to 1024031. How does this work? The derived table, n2, consists of all the numbers from 0 to 32 multiplied by 32001, i.e.

0
32001
64002
96003
….

This is cross-joined with the result of the CTE, n, which consists of all the numbers from 0 to 32000, and the result is the sum of the values from n and n2. Hence we get:

from n 0- 32000 with 0 from n2 to give 0 – 32000
from n 0- 32000 with 32001 from n2 to give 32001 – 64001
from n 0- 32000 with 64002 from n2 to give 64002 – 96002
from n 0- 32000 with 96003 from n2 to give 64003 – 96003
…..

to give the values 0 – 1,024,031. If more values are required then just increase the size of n2 by increasing the maximum value from 32.

Write SQL faster. As Nigel demonstrates, Common Table Expressions are one of the most powerful features to be introduced with SQL Server 2005. SQL Prompt, Red Gate’s code completion tool, offers full support for CTEs, including recursive CTEs and multiple CTEs within a single WITH statement, and will increase the speed and accuracy with which you create this and other SQL code.

Keep up to date with Simple-Talk

For more articles like this delivered fortnightly, sign up to the Simple-Talk newsletter

This post has been viewed 230320 times – thanks for reading.

  • Rate
    [Total: 335    Average: 4.4/5]
  • Share

Nigel Rivett

View all articles by Nigel Rivett

Related articles

Also in CTE

Time Slots - An Essential Extension to Calendar Tables

After answering many forum entries from developers asking for help with dealing with SQL that involved time intervals and ranges, Dwain dreamed of a generalized tool that sets up time slots of various sorts without the need to experiment; that could do the heavy lifting, so that developers could do aggregations and reports based on time intervals without the hard graft. Here is Dwain's dream made reality. … Read more

Also in SQL

Relational Algebra and its implications for NoSQL databases

With the rise of NoSQL databases that are exploiting aspects of SQL for querying, and are embracing full transactionality, is there a danger of the data-document model's hierarchical nature causing a fundamental conflict with relational theory? We asked our relational expert, Hugh Bin-Haad to expound a difficult area for database theorists.… Read more

Also in SQL Server

SQL Server System Functions: The Basics

Every SQL Server Database programmer needs to be familiar with the System Functions. These range from the sublime (such as @@rowcount or @@identity) to the ridiculous (IsNumeric()) Robert Sheldon provides an overview of the most commonly used of them.… Read more

Also in SQL Server 2005

SQL Server 2005 DDL Trigger Workbench

Robyn and Phil's latest workbench shows you how to track and log all database changes, including changes to tables, logins, users and queues, using SQL 2005 DDL triggers.… Read more
  • Andrew Clarke

    Excellent Stuff!
    How nice to see the famous Nigel Rivett ‘in harness’. It is just the sort of article I want to read. It is about time he was featured as ‘Geek of the Week’.

  • Anonymous

    What else can CTE do?
    Interesting.

    Is there something else beyond recursion that a CTE can do?
    Does recursion with CTE any better performance wise that a controlled loop?

  • Anonymous

    re What else can CTE do?
    In general a CTE should be faster than a loop but it probably depends on what you are doing and how you code it.

    As to other things – a CTE is just a table that can be used in the following query. That is quite useful on it’s own as it does away with the need to repeat code.
    The recursion bit is an extra and there are a lot of things that can be done with it by varying what is returned by the various union’d clauses.

  • Anonymous

    great article
    I have been poking a little into CTE’s. From the limited examples I have seen elsewhere, I didn’t really see much that I didn’t already have a reasonable approach to handle. After reading this, I’ll take the time to play some more.

  • Anonymous

    Great Explanations and Examples
    I used to use derived tables all the time. Now that we have upgraded to SQL Server 2005, I have started using CTEs and LOVE them even more than derived tables. CTEs can really help clean up a complicated query and make it easier to understand.

    I’ve read several articles on CTEs and while I understood the theory behind a recursive CTE, I was never able to get a good understanding of what was really going on. The explanations and real-life examples in this article were just perfect for helping me grasp how to use recursive CTEs. Many thanks!

    One of your examples will directly help one of my co-workers who just the other day told me about his web app which allows users to enter a comma list of values. My co-worker wanted to know if SQL Server 2005 had a way besides loops and ouside functions for converting such a list to a record set. I now have a good answer to that question.

  • Anonymous

    Very good article
    I have written som blogposts about CTE’s myself. Just would like to point out that
    option(maxrecursion 0) gives an unlimited number of recursions
    My own samples can be found at:
    http://codeinet.blogspot.com

  • nigelrivett

    re: recursion limit
    Thanks.
    You’re right I should have mentioned that 0 eliminates the limit.
    I don’t like using it as it can get you into trouble with unexpectedly long running CTEs.

    I place the blame squarely with the site editor (who is responsible for most of the quality of that article) for not pointing out the omission :).

  • Anonymous

    bug?
    In second example,

    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

    value of x is between 4 to 29. Then how come max of x is returning 2137058649.

    I tried min of x and it is returning correct value as 4

  • Anonymous

    bug?
    In second example,

    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

    value of x is between 4 to 29. Then how come max of x is returning 2137058649.

    I tried min of x and it is returning correct value as 4

  • nigelrivett

    re: bug?
    Good spot – it is a little odd isn’t it.
    The clue is in my comment that it is actually accessing the underlying table multiple times.

    If you look at the query plan you will see that it is returning an ordered resultset for the max (giving the max id from sysobjects) but unordered for the other accesses. It doesn’t do this in master – the query plan is different there due to the way the sysobjects view is presented.

    If you order the set in the CTE then the problem goes away
    with MyCTE(x)as(select top 10 x = id from sysobjects order by id)
    select x,
    maxx = (select max(x) from MyCTE) ,
    pct = 100.0 * x / (select sum(x) from MyCTE)
    from MyCTE

    Of course if the CTE query isn’t ordered then you can’t expect the result to be the same for each run – but you might expect it to be consistent for the query.

    I’ll mention it to the Powers That Be and see what they say.

  • nigelrivett

    bug – response from Microsoft
    Had a response from Umachandar Jayachandran.

    “In SQL Server, CTEs are similar to views in that the definition or query
    expression is replaced inline in the query in all references. So you don’t
    really get any results caching etc.”

    “In the standards, the WITH clause was mainly introduced to support recursive
    queries. And the results of query in the WITH clause results in a virtual
    table that can be used multiple times. This is different from syntax level
    substitution that happens now in SQL Server.”

    “I guess there is surely room for improvement if you compare with the
    standards. And depending on how you use CTE you can actually get confusing
    results if you think of it as a temporary result set that doesn’t change for
    the duration of execution of the query.

    So sounds like it’s just something you have to remember. Except with a recursive CTE the query is included in the following statement so can be calculated multiple times – not necessarily giving the same reult every time.
    The example given would be unlikely to turn up in the real world as it would probably have an order by and the problem goes away.

    It is worth remembering though as there are cases which could catch you out
    I can see
    with a(i)
    as
    (select i = rand()
    )
    select (select i from a), (select i from a)

    being used – this will produce two different rand values.
    Although
    with a(i)
    as
    (select i = rand()
    )
    select i, i from a

    produces one. I can imagine that this depends on the optimiser though and probably isn’t guaranteed. Would be nice to find a query that gave an example of that.

  • Anonymous

    Re: bug – response from Microsoft

    Thanks for the reply. It seems to be clear now. 🙂

  • Anonymous

    Excellent article
    I love this article and I have a question, it is possible to use a CTE do convert a table into a CSV?

  • Anonymous

    Excellent article
    I love this article and I have a question, it is possible to use a CTE do convert a table into a CSV?

  • Anonymous

    Excellent article
    I love this article and I have a question, it is possible to use a CTE do convert a table into a CSV?

  • nigelrivett

    re: convert a table into a CSV
    You wouldn’t use a CTE for this. It is simple to do from a query.
    I’ll write a brief article about how to do this shortly.

  • Anonymous

    How to put CTE in store procedure
    Hi, Thanks for the tips, but i try to put the MYCTE in store procedure but i get an error

  • nigelrivett

    re: How to put CTE in store procedure
    Not a lot to go on here but lets guess:

    You’re using sql server v2000?
    Your database is in v2000 compatability mode?
    The preceding statement has to be terminated with a semi-colon?

  • Anonymous

    Please explain
    Hello, sir,
    This is a good article.

    When I try the query:
    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

    I have to give varchar(8000). But I got error when I change to varchar(7999). What is reason for it?

  • Anonymous

    Sorry, please ingore my previous post
    Sorry, please ingore my previous post

  • Anonymous

    Dynamic SQL Queries
    Is there any way to execute dynamic sql queries inside With Statement?

    <a href=”http://www.netaffiliatebase.com”>http://www.netaffiliatebase.com</a&gt;

  • nigelrivett

    re: Dynamic SQL Queries
    I doubt it.
    You could probably populate a temp table from dynamic sql and access that or maybe try an openquery on a self linked database.

  • Anonymous

    Collapsing to CSV column
    >You wouldn’t use a CTE for this. It is simple
    >to do from a query.
    >I’ll write a brief article about how to do this
    >shortly.

    I saw your article on exporting to CSV, and I think that maybe the person was asking about collapsing a set of rows (grouped by something) into a CSV list in one column.

    Given a table “x” where “a” is the group by column there are mutliple rows per a with value column of “c”:

    declare @x table(a varchar(10), c varchar(10))
    insert into @x (a,c) values (‘a’,’m’);
    insert into @x (a,c) values (‘a’,’p’);
    insert into @x (a,c) values (‘a’,’o’);
    insert into @x (a,c) values (‘b’,’p’);
    insert into @x (a,c) values (‘b’,’q’);
    insert into @x (a,c) values (‘c’,null);
    insert into @x (a,c) values (‘d’,’x’);
    insert into @x (a,c) values (‘e’,’o’);
    insert into @x (a,c) values (‘e’,’q’);

    with ranked (a, rank, c)
    as (select a, rank() over(partition by a order by c), c
    from @x
    where c is not null),
    concat (a, rank, c)
    as ( –>base set
    select a, rank, cast(c as varchar(1000))
    from ranked
    where rank = 1
    union all
    –>recursive set
    select ranked.a, ranked.rank, cast(concat.c + ‘, ‘ + ranked.c as varchar(1000))
    from ranked
    inner join concat on ranked.a = concat.a and ranked.rank -1 = concat.rank),
    full_list_only (a, c)
    as (select a, c
    from concat
    join (select a, max(rank) max_rank from ranked group by a) max_rank
    on concat.a = max_rank.a and concat.rank = max_rank.max_rank)

    “select * from concat” would return:
    a rank c
    —————-
    a 1 m
    b 1 p
    d 1 x
    e 1 o
    e 2 o, q
    b 2 p, q
    a 2 m, o
    a 3 m, o, p

    “select * from full_list_only” would return:
    a c
    ——-
    d x
    e o, q
    b p, q
    a m, o, p

    I won’t bother explaining it – is just follwos the same logic as the other recursive CTE examples

  • Anonymous

    C# to Recursive CTE translation help
    I wrote an algorithm to get all the relatives of a person in a family tree. I’m basically getting all the users from the DB and am doing the recursive logic in code, so that there is only 1 call made to the DB. However, I am trying to do the same thing within a stored procedure in SQL using recursive CTEs (I think the performance might be better) but I’m finding it really tough to craft the CTE. I would really appreciate if someone could translate the following code to a recursive CTE in SQL. I tried to write the SP myself but I am not getting the right results. Here’s the (incorrect) SP, and just below that is the C# code that works correctly and an explanation of my schema:

    ALTER PROCEDURE [dbo].[spGetFamilyMembersForUser]
    @UserName varchar(50)
    AS
    BEGIN

    declare @familyid int
    set @familyid = (select AsChildFamilyID from Users where UserName = @UserName);

    WITH Ancestors (UserName, Gender, AsChildFamilyID, AsSpouseFamilyID, AsParentFamilyID) AS
    (
    — Base case (get father of user)
    SELECT u.UserName, u.Gender, u.AsChildFamilyID, u.AsSpouseFamilyID, u.AsParentFamilyID
    FROM Users u
    WHERE u.AsParentFamilyID = @familyid AND u.Gender = ‘Male’

    UNION

    — Base case (get mother of user)
    SELECT u.UserName, u.Gender, u.AsChildFamilyID, u.AsSpouseFamilyID, u.AsParentFamilyID
    FROM Users u
    WHERE u.AsParentFamilyID = @familyid AND u.Gender = ‘Female’

    UNION ALL

    — Recursive step
    SELECT u.UserName, u.Gender, u.AsChildFamilyID, u.AsSpouseFamilyID, u.AsParentFamilyID
    FROM Users u
    INNER JOIN Ancestors a ON a.AsChildFamilyID = u.AsParentFamilyID
    WHERE u.AsChildFamilyID <> -1
    )

    SELECT * FROM Ancestors
    WHERE Ancestors.UserName <> @UserName

    END

    //FYI

    1. _dtAllUsers contains all users
    2. _dtAllRelatives is initially empty, and is a clone of _dtAllUsers
    3. The Users table has the following columns:

    UserName, Gender, AsChildFamilyID, AsSpouseFamilyID, AsParentFamilyID

    AsChildFamilyID means the family(ID) in which the user is a child. Similarly for AsSpouseFamilyID and AsParentFamilyID.
    A value of -1 means that the user does not have that role in the family. For instance, if AsChildFamilyID is -1, then user is not a child in that family.

    Example:
    UserName, Gender, AsChildFamilyID, AsSpouseFamilyID, AsParentFamilyID

    Joe, Male, 1, -1, -1
    Jack, Male, 2, 1, 1
    Jill, Female, -1, 1, 1
    Jim, Male, -1, -1, 2

    In this example, Jack and Jill are Joe’s parents. Jim is Jack’s father.

    // POST-CONDITION

    1. _dtAllRelatives contains all relatives for user

    public void ComputeAllRelativesForUser(string userName)
    {
    DataTable dtUser = UserManager.GetUser(userName);

    int asChildFamilyID = (int)dtUser.Rows[0][“AsChildFamilyID”];
    int asSpouseFamilyID = (int)dtUser.Rows[0][“AsSpouseFamilyID”];
    int asParentFamilyID = (int)dtUser.Rows[0][“AsParentFamilyID”];

    string motherUserName = string.Empty; //the username of the user’s mother
    string fatherUserName = string.Empty;
    string spouseUserName = string.Empty;
    string siblingUserName = string.Empty;
    string childUserName = string.Empty;

    int fathersAsChildFamilyID = -1; //the AsChildFamilyID of user’s father
    int mothersAsChildFamilyID = -1;
    int spousesAsChildFamilyID = -1;
    int siblingsAsChildFamilyID = -1;
    int childsAsChildFamilyID = -1;

    foreach (DataRow row in _dtAllUsers.Rows)
    {
    //get Father
    if ((int)row[“AsParentFamilyID”] == asChildFamilyID && asChildFamilyID != -1 && (string)row[“Gender”] == “Male”)
    {
    _dtAllRelatives.Rows.Add(row);
    fatherUserName = (string)row[“UserName”];
    fathersAsChildFamilyID = (int)row[“AsChildFamilyID”];
    }

    //get Mother
    if ((int)row[“AsParentFamilyID”] == asChildFamilyID && asChildFamilyID != -1 && (string)row[“Gender”] == “Female”)
    {
    _dtAllRelatives.Rows.Add(row);
    motherUserName = (string)row[“UserName”];
    mothersAsChildFamilyID = (int)row[“AsChildFamilyID”];
    }

    //get Spouse
    if ((int)row[“AsSpouseFamilyID”] == asSpouseFamilyID && asSpouseFamilyID != -1)
    {
    _dtAllRelatives.Rows.Add(row);
    spouseUserName = (string)row[“UserName”];
    spousesAsChildFamilyID = (int)row[“AsChildFamilyID”];
    }

    //get Sibling
    if ((int)row[“AsChildFamilyID”] == asChildFamilyID && asChildFamilyID != -1)
    {
    _dtAllRelatives.Rows.Add(row);
    siblingUserName = (string)row[“UserName”];
    siblingsAsChildFamilyID = (int)row[“AsChildFamilyID”];
    }

    //get Child
    if ((int)row[“AsChildFamilyID”] == asParentFamilyID && asParentFamilyID != -1)
    {
    _dtAllRelatives.Rows.Add(row);
    childUserName = (string)row[“UserName”];
    childsAsChildFamilyID = (int)row[“AsChildFamilyID”];
    }

    //recursive for father
    if (fathersAsChildFamilyID != -1)
    ComputeAllRelativesForUser(fatherUserName);

    //recursive for mother
    if (mothersAsChildFamilyID != -1)
    ComputeAllRelativesForUser(motherUserName);

    //recursive for spouse
    if (spousesAsChildFamilyID != -1)
    ComputeAllRelativesForUser(spouseUserName);

    //recursive for sibling
    if (siblingsAsChildFamilyID != -1)
    ComputeAllRelativesForUser(siblingUserName);

    //recursive for child
    if (childsAsChildFamilyID != -1)
    ComputeAllRelativesForUser(childUserName);
    }
    }

  • Anonymous

    CTE is really Fantastic feature in Sql-Server 2005
    I have used CTE in my Project. It is very useful for linked Items.

  • Anonymous

    CTE is really Fantastic feature in Sql-Server 2005
    I have used CTE in my Project. It is very useful for linked Items at n level.

  • Anonymous

    thank u…
    nice article..it helped me a lot.
    thank you very much Nigel Rivett sir..

  • Michael

    Thanks
    Cool stuff!

  • Anonymous

    to count number of nodes
    I have a table stucture like this :-

    CREATE TABLE [dbo].[blogs_friend](
    [id] [bigint] IDENTITY(1,1) NOT NULL,
    [user_id] [uniqueidentifier] NOT NULL,
    [friend_id] [uniqueidentifier] NOT NULL,
    [friend_accepted] [char](1) COLLATE Latin1_General_CI_AS NOT NULL CONSTRAINT [DF_blogs_friend_friend_accepted] DEFAULT (‘N’)
    ) ON [PRIMARY]

    GO
    SET ANSI_PADDING OFF

    I want to count the number of people one person is connected to?

    i use this query to get a list of friends attached to a user

    friend_id = case when user_id = @id then friend_id when friend_id = @id then user_id end
    from dbo.blogs_friend
    where user_id =@id or friend_id = @id

  • Anonymous

    to count number of nodes
    I have a table stucture like this :-

    CREATE TABLE [dbo].[blogs_friend](
    [id] [bigint] IDENTITY(1,1) NOT NULL,
    [user_id] [uniqueidentifier] NOT NULL,
    [friend_id] [uniqueidentifier] NOT NULL,
    [friend_accepted] [char](1) COLLATE Latin1_General_CI_AS NOT NULL CONSTRAINT [DF_blogs_friend_friend_accepted] DEFAULT (‘N’)
    ) ON [PRIMARY]

    GO
    SET ANSI_PADDING OFF

    I want to count the number of people one person is connected to?

    i use this query to get a list of friends attached to a user

    friend_id = case when user_id = @id then friend_id when friend_id = @id then user_id end
    from dbo.blogs_friend
    where user_id =@id or friend_id = @id

  • nigelrivett

    re: to count number of nodes
    You have a hierarchy and need to traverse it at then count the number of entries.
    Traversing a hierarchy using a CTE is covered in bol (see the link in the above article) then just do a count(*) (or count distnict) on the result.

  • Hari

    Incorrect syntax near the keyword ‘with’ CTE expression or xmlnamespaces
    Hello Nigel,
    If i use an IF Exists statement before the CTE i get the abov error for some database and the same query works fine for some db. Is there some configuration change i need to do?

  • nigelrivett

    re: Incorrect syntax near the keyword ‘with’ CTE expression or xmlnamespaces
    You will need a ; before the cte definition.

    I don’t think it’s possible to use a CTE in a subquery.

    Are you sure it works for some databases?

  • Badri

    Nice Article
    Its a Nice article and very understandable

  • alan

    thanks
    A really wonderful article, thanks very much.

  • Anonymous

    MAXRECURSION issue
    I am not able to compile following function when i use MAXRECURSION option

    it gives error message
    “Incorrect syntax near the keyword ‘OPTION’.”
    Can u please as what is the issue here.

    ALTER FUNCTION [dbo].[CreateVarcharList](@sData varchar(8000), @sDelim char(1))
    RETURNS TABLE
    AS
    RETURN
    (WITH csvtbl(i,j)
    AS
    (
    SELECT i = 1, j = CHARINDEX(@sDelim, @sData + @sDelim)
    UNION ALL
    SELECT i = j + 1, j = CHARINDEX(@sDelim, @sData + @sDelim, j + 1)
    FROM csvtbl
    WHERE CHARINDEX(@sDelim, @sData + @sDelim, j + 1) <> 0
    )
    SELECT CAST(SUBSTRING(@sData, i, j – i) as Varchar) AS ListValue
    FROM csvtbl OPTION (MAXRECURSION 1000))

  • Anonymous

    MAXRECURSION issue
    I am not able to compile following function when i use MAXRECURSION option

    it gives error message
    “Incorrect syntax near the keyword ‘OPTION’.”
    Can u please as what is the issue here.

    ALTER FUNCTION [dbo].[CreateVarcharList](@sData varchar(8000), @sDelim char(1))
    RETURNS TABLE
    AS
    RETURN
    (WITH csvtbl(i,j)
    AS
    (
    SELECT i = 1, j = CHARINDEX(@sDelim, @sData + @sDelim)
    UNION ALL
    SELECT i = j + 1, j = CHARINDEX(@sDelim, @sData + @sDelim, j + 1)
    FROM csvtbl
    WHERE CHARINDEX(@sDelim, @sData + @sDelim, j + 1) <> 0
    )
    SELECT CAST(SUBSTRING(@sData, i, j – i) as Varchar) AS ListValue
    FROM csvtbl OPTION (MAXRECURSION 1000))

  • Anoop

    Can we use more than one CTEs?
    Can we use more than one CTEs?

    for eg:

    with MyCTE1(x1)as(select x1=’hello’)
    with MyCTE2(x2)as(select x2=’hello’)
    select x1 from MyCTE1
    union
    select x2 from MyCTE2

  • Anoop

    Can we use more than one CTEs?
    Can we use more than one CTEs?

    for eg:

    with MyCTE1(x1)as(select x1=’hello’)
    with MyCTE2(x2)as(select x2=’hello’)
    select x1 from MyCTE1
    union
    select x2 from MyCTE2

  • Elias

    See you…Thanks, its great to receive such positive comments.
    See you…Thanks, its great to receive such positive comments.
    <a href=http://big-boobs.yuoi.biz > big boobs </a>
    [url=http://buffie-the-body.yuoi.biz] buffie the body [/url] [url=http://beasiality.yuoi.biz] beastiality [/url]

  • Cores

    CTE can be referenced within views… How to reference in Crystal Reports?
    Thanks for excellent article and postings!!

    I manually recreate views that I used for Oracle for using now in Sql Server. Then I will reference these views in Crystal Reports.
    One of the views in Oracle contains CONNECT BY feature for hierarchy. I used CTE instead of CONNECT BY to solve this hierarhy problem for SQL Server. It works well, but the definition of this CTE is not stored in SQL Server metadata. How can I reference this CTE in Crystal Reports as a source?
    It is said, CTE can be referenced within views, how?? Do you have examples?
    Thank you!

  • Sarah Grady

    Anonymous Comments Disabled
    Due to high volume of spamming, anonymous comments have been disabled.

  • Gopal

    create view on Recursive CTE FAILS with option
    Following is my code –

    create view vw_cte as
    with cte(m) as
    (
    select datediff(m, ‘1900-01-01’, getdate())
    union all
    select m + 1 from cte where m < 2000
    )
    select m from cte
    option (maxrecursion 0)

    throws an error –
    Msg 156, Level 15, State 1, Procedure vw_cte, Line 9
    Incorrect syntax near the keyword ‘option’.

    What am I missing here?

  • hariaspind

    Regarding the Types don’t match between the anchor and the recursive part
    I tried with Varchar(max) as below ,i didn’t throw an error.It works fine

    with MyCTE(x)
    as(
    select x = convert(Varchar(max),’hello’)
    union all
    select x + ‘a’ from MyCTE where len(x) < 100
    )
    select x from MyCTE order by x

  • hariaspind

    Regarding the Types don’t match between the anchor and the recursive part
    I tried with Varchar(max) as below ,i didn’t throw an error.It works fine

    with MyCTE(x)
    as(
    select x = convert(Varchar(max),’hello’)
    union all
    select x + ‘a’ from MyCTE where len(x) < 100
    )
    select x from MyCTE order by x

  • Michael Mournier

    Doesn’t seem traditional recursion
    First of all congratulations to Nigel for such a detailed yet easy to grasp article on CTEs.
    However as far as concept of recursion in CTEs is concerned, this does not seems ‘traditional’ recursion to me.
    A traditional recursive function when executed keeps branching out into successive recursive calls until the leaf node (base condition) is reached. From the leaf node onwards the result is collected in reverse hierarchy until the root node is reached. This root node is the main function call. Thus ‘actual work’ starts only when the leaf node is reached ( or when the last recursive call is reached)

    An example of this traditional recursion would a a function to calculate factorial of a number. If the function were used to calculate 5 factorial, it would execute as below

    Return 5 * 4 Factorial
    Return 4 * 3 Factorial
    Return 3 * 2 Factorial
    Return 2 * 1 Factorial
    Return 1 [Leaf node reached]

    However in case of CTE recursion, the base condition is executed at the root itself. Also the recursion structure look more like a spiral ‘While’ loop rather than a tree structure.

    I hope what I say makes sense. In short though I am able to understand everything in the article, I am not able to decipher the recursion in CTE when I look at it from what I think ‘traditional’ recursion is.

  • Michael Mournier

    Doesn’t seem traditional recursion
    First of all congratulations to Nigel for such a detailed yet easy to grasp article on CTEs.
    However as far as concept of recursion in CTEs is concerned, this does not seems ‘traditional’ recursion to me.
    A traditional recursive function when executed keeps branching out into successive recursive calls until the leaf node (base condition) is reached. From the leaf node onwards the result is collected in reverse hierarchy until the root node is reached. This root node is the main function call. Thus ‘actual work’ starts only when the leaf node is reached ( or when the last recursive call is reached)

    An example of this traditional recursion would a a function to calculate factorial of a number. If the function were used to calculate 5 factorial, it would execute as below

    Return 5 * 4 Factorial
    Return 4 * 3 Factorial
    Return 3 * 2 Factorial
    Return 2 * 1 Factorial
    Return 1 [Leaf node reached]

    However in case of CTE recursion, the base condition is executed at the root itself. Also the recursion structure look more like a spiral ‘While’ loop rather than a tree structure.

    I hope what I say makes sense. In short though I am able to understand everything in the article, I am not able to decipher the recursion in CTE when I look at it from what I think ‘traditional’ recursion is.

  • psingla

    Excellent..
    simply superb,,
    Thanks a lot,,,

  • Dhaneenja

    limitation of CTE
    hi Nigel Rivett,
    thanks for sharing valuable information about CTE.i have notice some issue working with CTE. is there has any limitation working with XML. can you pls post what are the limitation of CTE.
    here is the error code when you apply CTE,

    —-
    DECLARE @XML_OLD xml

    SELECT @XML = N'<root><r>’ + replace(‘aaa.bbb.ccc.dddd.ee’, ‘.’, ‘</r><r>’) + ‘</r></root>’

    WITH MyCTE(col)
    AS
    (
    SELECT r.value(‘.’,’VARCHAR(25)’) AS col
    FROM @XML.nodes(‘//root/r’) AS RECORDS(r)
    )

    SELECT * FROM MyCTE
    go
    —-
    after execute above code it will return error.

    Thanks
    Tharindu Dhaneenja

Join Simple Talk

Join over 200,000 Microsoft professionals, and get full, free access to technical articles, our twice-monthly Simple Talk newsletter, and free SQL tools.

Sign up