Does CTE in SQL Server have a nesting limit?

TLDR; – no. The limit is your memory capabilities.

In below example the script runs a cte in loop. It iterates over a selected number of ctes; each cte refers to the previous one. As a result we retrieve 1 initial row defined at the beginning. Optionally we can measure the SQL performance recording the time when the row has been added (to a table variable). On a regular home laptop, the query produces a memory error after 2000 iterations. 
Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64)   Sep 24 2019 13:48:23   Copyright (C) 2019 Microsoft Corporation  Express Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 19045: )
  
SET NOCOUNT ON

DECLARE @CounterLimit int = 1000                --declare number of iterations
DECLARE @sql nvarchar(max)
DECLARE @finalsql nvarchar(max)

DECLARE @statstable table                       --declare statistics of measures
(id int,
timestamp datetime)


DECLARE @presql nvarchar(max)=                  --header of sql query, initializing cte1
'WITH 
cte1
AS
(SELECT
        1 AS id
      ,''val'' AS val)
'


DECLARE @Counter INT 
SET @Counter=1
WHILE ( @Counter <= @CounterLimit)             --while loop
BEGIN
    
    SET @sql =
    CONCAT(
    COALESCE(@sql,cast('' as nvarchar(max)))
        ,CAST(' ,cte' AS NVARCHAR(MAX))
        ,cast(@Counter+1 as nvarchar(max))
        ,cast('
        AS
        (SELECT
                * from cte' as NVARCHAR(MAX)) --iterating the names of cte
        ,cast(@Counter as nvarchar(max))
        ,cast(')
        ' as nvarchar(max))
        )

    insert into @statstable                   --while loop statistics
    values (@Counter,GETUTCDATE())

    SET @Counter  = @Counter  + 1
END



DECLARE @postsql nvarchar(max)=               --closing a query
cast('SELECT
    *
FROM cte' as nvarchar(max))
+cast(@CounterLimit as nvarchar(max))

set @finalsql = concat(@presql, @sql, @postsql)

-- select * from @statstable                  --optional statistics

exec sys.sp_executesql @finalsql
Back to main