Itzik Ben-Gan is always one of the names that come up when books about Transact SQL or training in T-SQL is mentioned. He is a co-founder of Solid Quality Mentors, An MVP since 1999, written several books on T-SQL, and has delivered numerous training events around the world focused on T-SQL Querying, Query Tuning and Programming.
The following questions were answered by Itzik via email.
---------------------------------------------------------------------
-- CTE Code That Creates Nested Sets Relationships
---------------------------------------------------------------------
DECLARE @root AS INT ;
SET @root=1 ;
-- CTE with two numbers: 1 and 2
WITH TwoNumsCTE
AS (SELECT
1 AS n
UNION ALL
SELECT
2
) ,
-- CTE with two binary sort paths for each node:
-- One smaller than descendants sort paths
-- One greater than descendants sort paths
SortPathCTE
AS (SELECT
empid, 0 AS lvl, n, CAST(n AS VARBINARY(MAX)) AS sortpath
FROM
dbo.Employees
CROSS JOIN TwoNumsCTE
WHERE
empid=@root
UNION ALL
SELECT
C.empid, P.lvl+1, TN.n,
P.sortpath
+CAST(ROW_NUMBER() OVER (PARTITION BY C.mgrid
-- *** determines order of siblings ***
ORDER BY C.empname, C.empid, TN.n) AS BINARY(4))
FROM
SortPathCTE AS P
JOIN dbo.Employees AS C
ON P.n=1
AND C.mgrid=P.empid
CROSS JOIN TwoNumsCTE AS TN
) ,
-- CTE with Row Numbers Representing sortpath Order
SortCTE
AS (SELECT
empid, lvl, ROW_NUMBER() OVER (ORDER BY sortpath) AS sortval
FROM
SortPathCTE
) ,
-- CTE with Left and Right Values Representing
-- Nested Sets Relationships
NestedSetsCTE
AS (SELECT
empid, lvl, MIN(sortval) AS lft, MAX(sortval) AS rgt
FROM
SortCTE
GROUP BY
empid, lvl
)
SELECT
*
FROM
NestedSetsCTE
ORDER BY
lft ;
GO
-- Output
|
empid |
lvl |
lft |
rgt |
|
——————— |
——————— |
——————— |
——————— |
|
1 |
0 |
1 |
28 |
|
2 |
1 |
2 |
13 |
|
5 |
2 |
3 |
8 |
|
8 |
3 |
4 |
5 |
|
10 |
3 |
6 |
7 |
|
4 |
2 |
9 |
10 |
|
6 |
2 |
11 |
12 |
|
3 |
1 |
14 |
27 |
|
7 |
2 |
15 |
26 |
|
11 |
3 |
16 |
17 |
|
9 |
3 |
18 |
25 |
|
14 |
4 |
19 |
20 |
|
12 |
4 |
21 |
22 |
|
13 |
4 |
23 |
24 |
-- DDL & Sample Data
SET NOCOUNT ON;
USE tempdb;
GO
IF OBJECT_ID('dbo.Employees') IS NOT NULL
DROP TABLE dbo.Employees;
GO
CREATE TABLE dbo.Employees
(
empid INT NOT NULL PRIMARY KEY,
mgrid INT NULL REFERENCES dbo.Employees,
empname VARCHAR(25) NOT NULL,
salary MONEY NOT NULL
);
INSERT INTO dbo.Employees(empid, mgrid, empname, salary)
VALUES(1, NULL, 'David', $10000.00);
INSERT INTO dbo.Employees(empid, mgrid, empname, salary)
VALUES(2, 1, 'Eitan', $7000.00);
INSERT INTO dbo.Employees(empid, mgrid, empname, salary)
VALUES(3, 1, 'Ina', $7500.00);
INSERT INTO dbo.Employees(empid, mgrid, empname, salary)
VALUES(4, 2, 'Seraph', $5000.00);
INSERT INTO dbo.Employees(empid, mgrid, empname, salary)
VALUES(5, 2, 'Jiru', $5500.00);
INSERT INTO dbo.Employees(empid, mgrid, empname, salary)
VALUES(6, 2, 'Steve', $4500.00);
INSERT INTO dbo.Employees(empid, mgrid, empname, salary)
VALUES(7, 3, 'Aaron', $5000.00);
INSERT INTO dbo.Employees(empid, mgrid, empname, salary)
VALUES(8, 5, 'Lilach', $3500.00);
INSERT INTO dbo.Employees(empid, mgrid, empname, salary)
VALUES(9, 7, 'Rita', $3000.00);
INSERT INTO dbo.Employees(empid, mgrid, empname, salary)
VALUES(10, 5, 'Sean', $3000.00);
INSERT INTO dbo.Employees(empid, mgrid, empname, salary)
VALUES(11, 7, 'Gabriel', $3000.00);
INSERT INTO dbo.Employees(empid, mgrid, empname, salary)
VALUES(12, 9, 'Emilia' , $2000.00);
INSERT INTO dbo.Employees(empid, mgrid, empname, salary)
VALUES(13, 9, 'Michael', $2000.00);
INSERT INTO dbo.Employees(empid, mgrid, empname, salary)
VALUES(14, 9, 'Didi', $1500.00);
CREATE UNIQUE INDEX idx_unc_mgrid_empid ON dbo.Employees(mgrid, empid);
GO
Do you know someone who deserves to be a Database Geek of the Week? Or perhaps that someone is you? Send me an email at editor@simple-talk.com and include "Database Geek of the Week suggestion" in the subject line.
