By: Ahmad Yaseen | Comments (1) | Related: More > Scripts
Problem
Common Table Expression, also known as a CTE, can be thought of as a derived table that is defined within the query execution scope, without being stored physically in the database and survives only for the running query duration. A CTE allows us to write more readable, maintainable complex queries, by dividing these queries into small blocks that can be gathered together to build the final complex query. The CTE can be easily included within stored procedures, views, triggers and functions.
One of the most useful benefits of the CTE is the ability to create a recursive query within it. This means that the CTE can be self-referenced within the same query. But if it is not designed carefully, it may result in an infinite loop. To prevent that, SQL Server set the default value of the recursion level to 100. In specific cases, you may exceed that default value of recursion. In this case the query will fail showing that you can’t exceed this 100 limitation. How could we tune the recursion value to fit our query?
Solution
Limiting the number of recursions allowed for a specific query in SQL Server with the 100 default value prevents the cause of an infinite loop situation due to a poorly designed recursive CTE query. But it also leaves the choice for you to tune that value to fit your query, where you can easily increase or decrease that value at the query level using the MAXRECURSION query hint in the OPTION clause. The MAXRECURSION value specifies the number of times that the CTE can recur before throwing an error and terminating.
You can provide the MAXRECURSION hint with any value between 0 and 32,767 within your T-SQL query, with MAXRECURSION value equal to 0 means that no limit is applied to the recursion level. This opens up the risk of an infinite loop with poorly written queries.
Let's start our demo to understand how it works practically. Assume that we have a user-defined function that is used to split the strings depending on the provided delimiter. The function is written using the recursive CTE mechanism. One of the ways that can be used to write a function is shown below:
USE MSSQLTipsDemo
GO
CREATE FUNCTION [dbo].[SplitString]
(
@String NVARCHAR(4000),
@Delimiter NCHAR(1)
)
RETURNS TABLE
AS
RETURN
(
WITH Split(stpos,endpos)
AS(
SELECT 0 AS stpos, CHARINDEX(@Delimiter,@String) AS endpos
UNION ALL
SELECT endpos+1, CHARINDEX(@Delimiter,@String,endpos+1)
FROM Split
WHERE endpos > 0
)
SELECT 'Value' = SUBSTRING(@String,stpos,COALESCE(NULLIF(endpos,0),LEN(@String)+1)-stpos)
FROM Split
)
This type of function is sometimes used for splitting strings in some systems. For example, the below stored procedure takes a list of comma separated entities as a parameter and returns these entities as individual entities:
USE MSSQLTipsDemo GO CREATE PROC REC_Entity (@Entity AS NVARCHAR(MAX)) AS BEGIN CREATE TABLE #Entity (RetValue NVARCHAR (3)) INSERT INTO #Entity SELECT * from [SplitString] (@Entity, ',') SELECT * FROM #Entity DROP TABLE #Entity END
Now, we will declare two entities lists; @ENTITY1 and @ENTITY2, @ENTITY1 contains about 24 comma separated entities and @ENTITY2 contains about 110 comma separated entities. We will pass these two parameters to the previously created stored procedure to split its values using the T-SQL script below:
DECLARE @ENTITY1 AS NVARCHAR(MAX) DECLARE @ENTITY2 AS NVARCHAR(MAX) SET @ENTITY1='AZZ,AZT,AZS,AZR,AZP,AZO,AZN,AZL,AZI,AZG,AZD,AZB,AZA,AYY,AYW,AYU,AYT,AYS,AYR,AYQ,AYP,AYO,AYN,AYM' SET @ENTITY2='AYL,AYK,AYI,AYG,AYD,AYC,AYA,AXX,AXV,AXU,AXT,AXS,AXR,AXP,AXN,AXM,AXL,AXK,AXJ,AXG,AXF,AXE,AXD,AXC,AXB,AXA,AWZ,AWR,AWP,AWN,AWM,AWK,AWE,AWD,AWB,AWA,AVX,AVW,AVV,AVU,AVP,AVO,AVN,AVL,AVK,AVI,AVG,AVB,AVA,AUZ,AUY,AUX,AUW,AUV,AUU,AUT,AUS,AUR,AUQ,AUP,AUO,AUN,AUM,AUL,AUK,AUJ,AUI,AUH,AUG,AUF,AUE,AUD,AUC,AUA,ATZ,ATY,ATX,ATW,ATV,ATU,ATT,ATS,ATR,ATQ,ATP,ATO,ATN,ATM,ATL,ATK,ATJ,ATI,ATH,ATG,ATF,ATE,ATD,ATC,ATB,ATA,ASZ,ASY,ASX,ASW,ASV,ASU,AST,ASR,ASQ,ASP' EXEC REC_Entity @ENTITY1 EXEC REC_Entity @ENTITY2
Running the previous query, you will see from the result that splitting the first parameter that contains 24 comma separated entities will be completed successfully, and splitting the second entities list will fail as shown in the result below:
Checking the error message returned from the query execution, you will see that splitting the second entities list failed due to exceeding the maximum recursion level default value of 100. The first entities list requires 24 recursions to be split, which is less than the 100 maximum number of recursions. Splitting the second entities list requires 110 recursions, exceeding the 100 default value of recursions. Recall that this recursion limitation is mainly used to protect the system from the infinite loop issue resulting from the poorly written queries. The error will be as shown below:
As we mentioned previously, although this limit will protect you from the infinite loop issue, you can easily tune the MAXRECURSION value to fit your query. You can use the MAXRECURSION query hint within the previously defined stored procedure in the part that calls the string splitting function to fit this query only, with MAXRECURSION value equal to 110, using the modified stored procedure below:
USE MSSQLTipsDemo GO ALTER PROC REC_Entity (@Entity AS NVARCHAR(MAX)) AS BEGIN CREATE TABLE #Entity (RetValue NVARCHAR (3)) INSERT INTO #Entity SELECT * from [SplitString] (@Entity, ',') OPTION (MAXRECURSION 110) SELECT * FROM #Entity DROP TABLE #Entity END
Executing the modified stored procedure again to split the same two entities lists using the T-SQL script below:
DECLARE @ENTITY1 AS NVARCHAR(MAX) DECLARE @ENTITY2 AS NVARCHAR(MAX) SET @ENTITY1='AZZ,AZT,AZS,AZR,AZP,AZO,AZN,AZL,AZI,AZG,AZD,AZB,AZA,AYY,AYW,AYU,AYT,AYS,AYR,AYQ,AYP,AYO,AYN,AYM' SET @ENTITY2='AYL,AYK,AYI,AYG,AYD,AYC,AYA,AXX,AXV,AXU,AXT,AXS,AXR,AXP,AXN,AXM,AXL,AXK,AXJ,AXG,AXF,AXE,AXD,AXC,AXB,AXA,AWZ,AWR,AWP,AWN,AWM,AWK,AWE,AWD,AWB,AWA,AVX,AVW,AVV,AVU,AVP,AVO,AVN,AVL,AVK,AVI,AVG,AVB,AVA,AUZ,AUY,AUX,AUW,AUV,AUU,AUT,AUS,AUR,AUQ,AUP,AUO,AUN,AUM,AUL,AUK,AUJ,AUI,AUH,AUG,AUF,AUE,AUD,AUC,AUA,ATZ,ATY,ATX,ATW,ATV,ATU,ATT,ATS,ATR,ATQ,ATP,ATO,ATN,ATM,ATL,ATK,ATJ,ATI,ATH,ATG,ATF,ATE,ATD,ATC,ATB,ATA,ASZ,ASY,ASX,ASW,ASV,ASU,AST,ASR,ASQ,ASP' EXEC REC_Entity @ENTITY1 EXEC REC_Entity @ENTITY2
You will see that the two stored procedure calls are executed successfully and the two comma separated entities lists are split completely without showing any error as shown in the result below:
To be more dynamic, it is better to tune the MAXRECURSION value in the previous stored procedure with the maximum number of entities that can be passed to that stored procedure. For example, if you are dealing with countries that will not have more than 500 entities, you can replace the previous query hint with OPTION (MAXRECURSION 500), setting 500 as the maximum allowed recursion level for that query, and at the same time protecting your system from the infinite loop issue, by terminating the query if it exceeds that number of recursions.
If your query requires recursion level greater than the maximum allowed MAXRECURSION value, which is 32,767, you can achieve that by breaking the MAXRECURSION limitation by setting the MAXRECURSION value to 0 using the OPTION (MAXRECURSION 0) query hint. In this way, you will take the risk of falling in the infinite loop problem if the CTE recursion query is not written well.
Next Steps
- Check How to use SQL Server CTEs to make your T-SQL code readable by humans.
- Read more about SQL Server Queries With Hints.
About the author
Ahmad Yaseen is a SQL Server DBA with a bachelor’s degree in computer engineering as well as .NET development experience.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
