Selecting from hierarchies like Managers and Employees

Question

Selecting from hierarchies like Managers and Employees

Chuck Hoffman-291200

SSC Veteran

Points: 234
More actions
May 2, 2012 at 9:49 pm

#255259

Comments posted to this topic are about the item Selecting from hierarchies like Managers and Employees
Chuck Hoffman
------------
I'm not sure why we're here, but I am sure that while
we're here we're supposed to help each other
------------

Viewing 15 posts - 1 through 15 (of 49 total)

You must be logged in to reply to this topic. Login to reply

miika.langille SSC Rookie Points: 26 More actions · Answer 1

Isn't this same function much easier to realise with an rCTE? Does an rCTE have some hidden overhead which makes manual looping better?

For downstream selection, you could have something along the lines of:

WITH employmentTree

(EmpId, EmpName, SupId, [Level])

AS (

-- Anchor

SELECT EmpId, EmpName, SupId, 0

FROM Employees

WHERE EmpId = @targetEmpId

-- Recursive part

UNION ALL

SELECT emp.EmpId, emp.EmpName, emp.SupId, eTree.[Level] + 1

FROM Employees emp INNER JOIN employmentTree eTree

ON emp.SupId = eTree.EmpId)

SELECT EmpId, EmpName, SupId, [Level]

FROM employmentTree

imarran SSC-Addicted Points: 489 More actions · Answer 2

yes, I have done this often with rCTE's which is much faster 🙂

novak 32871 Valued Member Points: 66 More actions · Answer 3

novak 32871

Valued Member

Points: 66

May 3, 2012 at 12:56 am

#1482823

Why not simply use hierarchyid if it's T-SQL ?

Dave Brooking Ten Centuries Points: 1373 More actions · Answer 4

The HIERARCHYID datatype was only introduced to T-SQL in 2008. If you have to use an earlier version of SQL Server (2005 or earlier), hierarchyid is not supported.

Dave

Chuck Hoffman-291200 SSC Veteran Points: 234 More actions · Answer 5

Isn't this same function much easier to realise with an rCTE?

Thank you Miika.

I think the choice of approach must depend upon the environment. For a shop whose TSQL writers are at the expert level I like your solution better than mine. In a shop that has few expert level writers the rCTE would probably be difficult for many of them to read, making the code actually less maintainable.

I should admit that I had not thought about rCTE.

Chuck Hoffman
------------
I'm not sure why we're here, but I am sure that while
we're here we're supposed to help each other
------------

novak 32871 Valued Member Points: 66 More actions · Answer 6

I know but there is already SQL 2012 out. It is good exercise but 2000/2003 has it's age and most of clients already moved. But I understand your point.

imarran SSC-Addicted Points: 489 More actions · Answer 7

I also have a solution in the form of a view that you can join to that will display upline and down line structure depending on how it is joined to

Its quite technical but if you guys are keen I will submit it through an article

Dave Brooking Ten Centuries Points: 1373 More actions · Answer 8

Unfortunately, I have to work with a 3rd party application that has only recently been certified by the supplier to work with SQL Server 2008. The base application probably contains none of the datatypes that have been introduced to TSQL since SQL Server 2000. Some of the companies using the application are still running it on SQL Server 2000. We are running SQL Server 2005, so we can at least use CTEs for reporting.

If we want to take advantage of any of the newer datatypes we would have to introduce our own tables, the links with each table could possibly break with each new release of the application. I don't think introducing tables containing HIERARCHYID columns worth it at the moment as there are other ways to report on the data (we are currently using recursive CTEs). However I am considering adding tables containing spatial datatypes as I think the benefit could outweigh the effort and also the risk of a new release breaking the table linkage.

I'd be interested in knowing which version(s) of SQL Server Chuck is using/supporting.

Dave

Jonathan AC Roberts SSCoach Points: 18333 More actions · Answer 9

If you are using a recursive CTE then you will need a to have a UNION ALL.

DECLARE @EmpId int

SET @EmpId = 5

;WITH CTE AS

(

SELECT *

FROM employee e

WHERE e.EmpId = @EmpId

UNION ALL

SELECT e.*

FROM employee e

INNER JOIN CTE

ON CTE.SupId = e.EmpId

)

SELECT *

FROM CTE

OPTION (MAXRECURSION 0)

You can also add MAXRECURSION 0 if you are confident that there is no infinite loop in the expression or data, if there is it could lead to an infinite loop, e.g. if you had an insert for Baker like this:

INSERT INTO Employee (EmpID, EmpName, SupID)

VALUES (3, 'Baker', 5)

So maybe best to stick to the default MAXRECURSION of 100 as I doubt there is any organisation with more than 100 levels.

ssandler SSC Enthusiast Points: 101 More actions · Answer 10

I use recursive CTEs for this as well and it's quite fast. Haven't tried the hierarchyid type though.

Chuck Hoffman-291200 SSC Veteran Points: 234 More actions · Answer 11

Dave Brooking (5/3/2012)
I'd be interested in knowing which version(s) of SQL Server Chuck is using/supporting.

2000, 2005, and 2008.

Chuck Hoffman
------------
I'm not sure why we're here, but I am sure that while
we're here we're supposed to help each other
------------

hayden-508426 SSC Rookie Points: 34 More actions · Answer 12

I use rCTE for account control and access on our websites. Best way to go, haven't found one that's faster, including hierarchyid. I'm thinking hierarchyid loses on my perf tests due to the majority of its functions being CLR.

Jeff Moden SSC Guru Points: 1004284 More actions · Answer 13

imarran (5/3/2012)
yes, I have done this often with rCTE's which is much faster 🙂

Proof!!! Let's see the proof for your claim!!!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004284 More actions · Answer 14

hayden-508426 (5/3/2012)
I use rCTE for account control and access on our websites. Best way to go, haven't found one that's faster, including hierarchyid. I'm thinking hierarchyid loses on my perf tests due to the majority of its functions being CLR.

Your performance tests? Care to share those please? 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)