none
Capacity of CTE RRS feed

  • Question

  • Hi,

    Is there a limit of records that CTE can handle?

    If my statement is only like below with 5 million of records selected, will CTE have a problem?

    With CTE AS (Select * from table1);

    SELECT *

    FROM CTE;


    cherriesh

    Monday, October 28, 2013 4:59 AM

Answers

  • Few things:

    1. Your statment is not correct. You do not need ";" after the CTE definition

    With CTE AS (Select * from table1)--;

    SELECT *

    FROM CTE;

    2. I dont think there is any issues using CTE liek this way. But, I would go against if that is unnecessary. In your case, you do not really need CTE, hence I would say, do not use it, better you may query it table. 


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked as answer by cherriesh Monday, October 28, 2013 6:06 PM
    Monday, October 28, 2013 5:07 AM
  • No limit.

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Marked as answer by cherriesh Monday, October 28, 2013 6:05 PM
    Monday, October 28, 2013 7:30 AM
    Answerer
  • As mentioned, if you just using CTEs like a basic view, there is no limit.  Where CTEs do have limitations is when you are using a recursive CTE, ie a CTE that refers to itself with a UNION (ALL) clause.  These have a built-in limitation of 100 levels of recursion, eg this will fail:

    ;WITH cte AS (
    SELECT 1 x
    UNION ALL
    SELECT x + 1
    FROM cte 
    WHERE x < 102 )
    SELECT *
    FROM cte
    OPTION ( MAXRECURSION 100 )
    But if you set the 102 to 100, the statement will succeed.  You can alter the recursion limit up to a maximum of 32,767. You can also disable it, but this is not advisable.
    • Marked as answer by cherriesh Monday, October 28, 2013 6:05 PM
    Monday, October 28, 2013 3:40 PM
  • A non-recursive CTE is simply a subquery.  So there is no limit on the size of data returned.

    Your query is exactly the same as writing:

    SELECT *
    FROM (Select * from table1);

    • Marked as answer by cherriesh Monday, October 28, 2013 6:06 PM
    Monday, October 28, 2013 3:48 PM
    Moderator

All replies

  • Few things:

    1. Your statment is not correct. You do not need ";" after the CTE definition

    With CTE AS (Select * from table1)--;

    SELECT *

    FROM CTE;

    2. I dont think there is any issues using CTE liek this way. But, I would go against if that is unnecessary. In your case, you do not really need CTE, hence I would say, do not use it, better you may query it table. 


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked as answer by cherriesh Monday, October 28, 2013 6:06 PM
    Monday, October 28, 2013 5:07 AM
  • No limit.

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Marked as answer by cherriesh Monday, October 28, 2013 6:05 PM
    Monday, October 28, 2013 7:30 AM
    Answerer
  • As mentioned, if you just using CTEs like a basic view, there is no limit.  Where CTEs do have limitations is when you are using a recursive CTE, ie a CTE that refers to itself with a UNION (ALL) clause.  These have a built-in limitation of 100 levels of recursion, eg this will fail:

    ;WITH cte AS (
    SELECT 1 x
    UNION ALL
    SELECT x + 1
    FROM cte 
    WHERE x < 102 )
    SELECT *
    FROM cte
    OPTION ( MAXRECURSION 100 )
    But if you set the 102 to 100, the statement will succeed.  You can alter the recursion limit up to a maximum of 32,767. You can also disable it, but this is not advisable.
    • Marked as answer by cherriesh Monday, October 28, 2013 6:05 PM
    Monday, October 28, 2013 3:40 PM
  • A non-recursive CTE is simply a subquery.  So there is no limit on the size of data returned.

    Your query is exactly the same as writing:

    SELECT *
    FROM (Select * from table1);

    • Marked as answer by cherriesh Monday, October 28, 2013 6:06 PM
    Monday, October 28, 2013 3:48 PM
    Moderator
  • thanks a lot!!!

    cherriesh

    Monday, October 28, 2013 6:05 PM