SQL Server Common Table Expressions (CTE) in sqlcmd and DbSchema

Nesting a derived table inside a derived table inside a third one works right up until someone has to change the innermost query. WITH lets you pull each subquery out, give it a name and a column list, and then write a plain query against the names. SQL Server 2022 calls a named result set like that a common table expression, and the same construct is the only way to write a query that walks a hierarchy of unknown depth.

What a common table expression is

A CTE is a temporary named result set, defined within the execution scope of a single SELECT, INSERT, UPDATE, MERGE or DELETE statement. It can also go in a CREATE VIEW statement, as part of the SELECT that defines the view. When the statement finishes, the name is gone.

Nothing is stored for it. The WITH common_table_expression page states that query results from CTEs are not materialized, and that each outer reference to the name re-executes the query behind it. Reference the same CTE twice in one statement and its query runs twice, which is when Microsoft's own guidance is to use a temporary table instead.

Writing a CTE and the statement that follows it

The examples run against one table of employees, each row carrying the id of the person it reports to:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    ManagerID INT NULL REFERENCES Employees(EmployeeID)
);

INSERT INTO Employees VALUES
  (1, 'John', 'Doe', NULL),
  (2, 'Jane', 'Smith', 1),
  (3, 'James', 'Johnson', 1),
  (4, 'Patricia', 'Brown', 2),
  (5, 'Robert', 'Davis', 2),
  (6, 'Linda', 'Miller', 3);

A CTE opens with WITH, names itself, optionally lists its columns, and puts its query in parentheses after AS. The statement that reads it comes straight afterwards, with nothing in between:

WITH Employee_CTE (EmployeeID, FirstName, LastName)
AS
(
    SELECT EmployeeID, FirstName, LastName
    FROM Employees
    WHERE EmployeeID < 5
)
SELECT * FROM Employee_CTE;

The column list after the name is optional when every column in the query already has a distinct name of its own. Where two joined tables both supply an EmployeeID, or where a column is an expression, the list is the place to give each one a name.

Types of CTEs

A recursive CTE needs at least two query definitions joined by UNION ALL: an anchor member, which produces the starting rows, and a recursive member, which references the CTE name. The recursive member runs again and again, each pass taking the previous pass's output as its input, and recursion stops when it returns no rows.

That is how you walk the ManagerID chain without knowing how deep it goes:

WITH OrgChart (EmployeeID, FirstName, ManagerID, EmployeeLevel)
AS
(
    SELECT EmployeeID, FirstName, ManagerID, 0
    FROM Employees
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.EmployeeID, e.FirstName, e.ManagerID, o.EmployeeLevel + 1
    FROM Employees AS e
    INNER JOIN OrgChart AS o ON e.ManagerID = o.EmployeeID
)
SELECT EmployeeID, FirstName, ManagerID, EmployeeLevel
FROM OrgChart
ORDER BY EmployeeLevel, EmployeeID;

A recursive member is restricted in what it may contain: no SELECT DISTINCT, GROUP BY, HAVING, TOP, PIVOT, scalar aggregation, subqueries, or outer joins, and it may name the CTE only once in its FROM clause. Every column a recursive CTE returns is nullable, whatever the underlying columns allow.

Restrictions on using CTEs

A CTE must be followed by a single SELECT, INSERT, UPDATE, MERGE or DELETE statement that references some or all of its columns. Where the CTE sits in the middle of a batch, the statement before it has to end in a semicolon. Writing the keyword as ;WITH is the habit that saves you from finding out the hard way.

Four clauses cannot appear inside the CTE's own query:

• ORDER BY, unless a TOP or OFFSET/FETCH clause is also there
• INTO
• OPTION with query hints, which belongs on the outermost SELECT
• FOR BROWSE

A CTE cannot contain another WITH clause, so CTEs do not nest: a second CTE goes after a comma in the same WITH clause, where it may reference the one before it. Forward references are rejected. You also cannot execute a stored procedure inside a CTE.

Permissions required for using CTEs

There is nothing to grant on a CTE itself, and the WITH common_table_expression page carries no permissions section: SQL Server creates no object for the name. What the surrounding statement needs are the permissions it would need anyway on the tables the CTE reads. For a SELECT, the SELECT page gives that as SELECT permission on each table or view, which can be inherited from SELECT on the schema or CONTROL on the table, or come from membership in db_datareader, db_owner or sysadmin.

Advantages and limitations of CTEs

Two things a CTE gives you that a derived table does not: a name at the top of the statement rather than a block of parentheses in the middle, and recursion. A derived table cannot reference itself, so the org chart above has no subquery equivalent.

Two things to watch. Each outer reference re-runs the CTE's query, so a CTE named three times in one statement is three executions of it, and a temporary table is the cheaper choice there. A badly composed recursive member loops forever, which SQL Server stops at the server-wide default of 100 recursion levels; OPTION (MAXRECURSION n) on the outer statement sets your own limit, from 0 for no limit up to 32767.

Running a CTE in sqlcmd and in DbSchema

Put the CTE and the statement that follows it in a file, query.sql, then run the file with the sqlcmd utility, substituting your own server, database, login and password:

sqlcmd -S <server> -d <database> -U <username> -P <password> -i query.sql

The rows print in the console, in the same shape as the result table above.

Design and visualize
your database schema

Edit referenced records
in related tables

Query your data
visually too

Reuse the SQL
generated

Free Download

DbSchema connects to SQL Server, reverse-engineers the database into a design model and draws the tables on a diagram, which is worth having open next to a recursive CTE: the foreign key from Employees.ManagerID back to Employees.EmployeeID is the line the recursion follows. Reverse-engineering reads the database into the model and writes nothing back.

Open the SQL Editor from the Editors menu, paste the CTE and its SELECT, and click Execute Query, which runs the statement at the cursor and shows the result as a table. Press Ctrl+Space anywhere in the editor for auto-complete on table and column names from the connected schema. Nothing here changes the design model: the statement runs against the database, and the SQL History pane keeps a record of it for the session.

Reach for a CTE when a subquery has stopped being readable, and for a recursive CTE when the depth of the data decides the number of joins. Everything this article used sits in the free Community Edition of DbSchema: the connection, the reverse-engineered diagram, and the SQL Editor the two queries run in. Get it from https://dbschema.com/download.html and paste in the recursive one first.

Sources

1. WITH common_table_expression (Transact-SQL)
2. SELECT (Transact-SQL)
3. sqlcmd utility
4. DbSchema SQL Editor