Only this week, get the SQL Complete Track of 9 courses in a special prize of $330 $89!
Infinite looping
15. TOP keyword
Processing trees with recursive CTEs
Processing graphs with recursive ctes


You have seen a way to change the behavior of SQL Server and how to make it go deeper (or shallower) into recursion. However, you shouldn't rely on the maximum recursion depth to terminate your query. You should always make sure that your T-SQL query terminates.

You can use another keyword, TOP n, to limit your query. The n is the maximal number of rows you expect. Even if your query does not contain a termination check (or it does, but the check is faulty), TOP will take care of query termination:

WITH Counter(PreviousValue) AS (
    1  -- anchor member
    PreviousValue + 1  -- recursive member
  FROM Counter

SELECT TOP 10  -- termination check
FROM Counter;  -- invocation


Run the template query with TOP and see for yourself how it works.