liveBook · Manning

ROW_NUMBER function

As explained in 100 SQL Server Mistakes and How to Avoid Them

ROW_NUMBER() is a window function that assigns a sequential integer to rows within an ordered window partition, optionally restarting for each partition.

ROW_NUMBER() generates incremental integers within each ManagerID partition. In an employee hierarchy, those values can become the next path component when constructing HIERARCHYID values recursively. section 3.3

Numbering employees within manager branches

A temporary table captures each employee and manager, then assigns an incremental value per manager: SELECT EmployeeID, ManagerID, ROW_NUMBER() OVER (PARTITION BY ManagerID ORDER BY ManagerID) AS Incremental INTO #Hierarchy FROM dbo.Employees. A recursive CTE uses Incremental to extend each manager's hierarchy path before updating the Employees table. section 3.3

Use the function here to:

  • Create sibling positions separately for each ManagerID. section 3.3
  • Consider ROW_NUMBER() as an alternative technique when removing duplicates with DISTINCT causes performance issues. section 5.9

How 100 SQL Server Mistakes and How to Avoid Them covers it

How deep it goes
Shows partitioning inside a temporary-table query and recursive CTE, but does not develop window-function theory.
Examples
Uses MagicChoc employees, ManagerID partitions, and generated hierarchy IDs updated through a recursive CTE.
What you can do after
Build it from scratch by generating per-manager increments for a recursive HIERARCHYID update.
How it connects
Links ManagerID partitioning to recursive CTEs, temporary tables, HIERARCHYID, and duplicate removal.
Cover of 100 SQL Server Mistakes and How to Avoid Them
liveBookmanning.com
Read 100 SQL Server Mistakes and How to Avoid Them on liveBook Get the book at manning.com

Related concepts

This explainer was generated by Manning's knowledge graph from the text of 100 SQL Server Mistakes and How to Avoid Them. The section links point to the parts of the book it draws on.