2

I'm writing a long-winded querying system that makes use of CTEs to build valid SQL. There's a possibility it could create up to a thousand expressions within even a simple system, and I'm worried I may hit a limit in the future and have to rework the system.

I've searched high and low but I can't find the answer. What I want to know is:
What is the maximum number of expressions allowed in a CTE?

NOTE: I am not using recursion.

My best guess is I'm limited by the number of characters allowed per batch (see Maximum size for a SQL Server Query? IN clause? Is there a Better Approach)

EDIT

Test code below. My development machine oddly stopped at 600 exactly.

DECLARE @i int = 1,  @q varchar(max);

SET @q = 'WITH[0]AS(SELECT 1 AS c)'

WHILE @i < 600
BEGIN
    SET @q = @q + ',[' + CONVERT(varchar, @i) + ']AS(SELECT * FROM[' + CONVERT(varchar, @i-1) + '])';
    SET @i = @i + 1;
END;

SET @q = @q + 'SELECT * FROM[' + CONVERT(varchar, @i-1) + ']';

EXEC (@q);

I'll see about getting this tested on production servers, but that will take a while.

Community
  • 1
  • 1
Codesleuth
  • 10,321
  • 8
  • 51
  • 71
  • 3
    I just tried with 1,000 very simple CTEs on my dev machine and got `There is insufficient system memory in resource pool 'default' to run this query.` so why not test your end? The query I tested was `;WITH T0(C) AS (SELECT 1),T1(C) AS (SELECT * FROM T0),T2(C) AS (SELECT * FROM T1), /* ... */ T1000(C) AS (SELECT * FROM T999) SELECT * FROM T1000` – Martin Smith Jun 28 '12 at 10:05
  • My question is asking what the hard, programmed limit is. If I wanted to test how far my dev environment can go, I would have done that. Since I cannot control production hardware, I need to know of any limit that had been set. – Codesleuth Jun 28 '12 at 10:17
  • 2
    Well I'm not sure there is one specifically but if you test in an environment with similar specs and encounter a memory error or `Internal error: An expression services limit has been reached. Please look for potentially complex expressions in your query, and try to simplify them.` then clearly you know the whole idea is a complete non starter anyway. – Martin Smith Jun 28 '12 at 10:32

1 Answers1

2

It seems there's no direct upper bound on the number of non-recursive CTEs you can define within a batch. You should be restricted only by the resources available and any other applicable limitations.

I spent a while researching, and this is the only direct answer to the question I could find.

However, there is no mention of a maximum number of CTEs in the MSDN list of SQL server maximums, or the CTE documentation.

Martin's tests mentioned in the question's comments suggest that there isn't a specific limit for this, since he got an out of memory error, rather than a limit error. I don't think it's odd at all that your test stopped at 600 exactly, since you're using WHILE @i < 600. Try upping the limit and seeing what happens.

Esoteric Screen Name
  • 6,082
  • 4
  • 29
  • 38
  • 600 was there because when I set it higher I got the resources error - it was completely intentional that I pasted at that value. Extra testing I've done since the updated post leads me to believe that you are correct about there being no limit beyond resources. I wrote a query that used real tables and the execution plan was so large it didn't load, but it ran, with more than 600. That was on the same machine. Perhaps the limit lies on the plan (depth?) – Codesleuth Jun 29 '12 at 13:07
  • Ah, that makes sense. I assume you mean the execution plan didn't load in SSMS? If so, that's probably hitting an SSMS limit, though I don't know offhand what that might be. Perhaps the XML max? I know SSMS can display QPs as XML, but I'm not sure if that's the underlying datatype. Are you able to examine the plan with the metadata objects or a tool like Sentry Plan Explorer? http://www.sqlsentry.com/plan-explorer/sql-server-query-view.asp – Esoteric Screen Name Jun 29 '12 at 14:15