Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

Recursive Queries in MS SQL Server

Introduction
Infinite looping
15. TOP keyword
Processing trees with recursive CTEs
Processing graphs with recursive ctes
Summary

Instruction

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 (
  SELECT
    1  -- anchor member
  UNION ALL
  SELECT
    PreviousValue + 1  -- recursive member
  FROM Counter
)

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

Exercise

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