InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 10:57:48 GMT20http://www.sqlservercentral.com/Forums/Topic1075989-203-1.aspxExcellent article Jeff! You just saved me quite a bit of sanityI was needing to sort by an alphanumeric name field and finally found a suitable way that doesn't seem to break.Here it is if anyone else has need of it.The main difference is the HierarchicalPath. You could essentially put ANY column in the OVER(ORDER BY) function and get a perfect sort.Mine assumes I'll never have more than 99999 items.[code="sql"]WITH DirectPrograms (ProgramParent, ProgramID, ProgramDesc, ProgramInactive, ProgramAdmin, [Level], HierarchicalPath)AS(SELECT p.ProgramParent, p.ProgramID, p.ProgramDesc, p.ProgramInactive, p.ProgramAdmin, [Level] = 0, HierarchicalPath = CAST('\'+RIGHT('00000' + CAST((ROW_NUMBER() OVER (ORDER BY p.ProgramDesc)) AS VARCHAR(5)), 5) AS VARCHAR(100)) FROM ys2.PROGRAM AS p WHERE ProgramParent IS NULLUNION ALLSELECT p.ProgramParent, p.ProgramID, p.ProgramDesc, p.ProgramInactive, p.ProgramAdmin, [Level] = [Level] + 1, HierarchicalPath = CAST(d.HierarchicalPath + '\'+RIGHT('00000' + CAST((ROW_NUMBER() OVER (ORDER BY p.ProgramDesc)) AS VARCHAR(5)), 5) AS VARCHAR(100)) FROM ys2.PROGRAM AS pINNER JOIN DirectPrograms AS d ON p.ProgramParent = d.ProgramID)SELECT ProgramID, ProgramDesc, ProgramInactive, ProgramAdmin, [Level], HierarchicalPath FROM DirectProgramsORDER BY HierarchicalPath[/code]Wed, 30 Nov 2011 15:53:49 GMTsykiemikeyhttp://www.sqlservercentral.com/Forums/Topic1075989-203-1.aspx@Sean,Did you get my email?Tue, 15 Mar 2011 15:34:45 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1075989-203-1.aspx[quote][b]Craig Farrell (3/14/2011)[/b][hr]AH! Legal Pyramid Schemes! Gotcha![/quote]Heh... I have mixed emotions about them. The ones that run a Uni-Level payout matrix and require you to sell product and not just stack up representatives are some of the best. People CAN make money without being at the "top". The people who fail to run their business correctly (usually boils down to being lazy) seem to get the most press and, of course, they have nothing good to say about the companies.Don't get me wrong... there are some really bad, bad, bad ones out there, as well but the industry, in general, has a legacy reputation that it no longer deserves.Mon, 14 Mar 2011 21:52:49 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1075989-203-1.aspx[quote][b]Jeff Moden (3/14/2011)[/b][hr][quote][b]Craig Farrell (3/14/2011)[/b][hr]A bit ignorant here, what's an MLM?[/quote]Multi-Level Marketing. Think, Amway, ACN, Avon, and a whole host of other companies similar in nature.[/quote]AH! Legal Pyramid Schemes! Gotcha!Mon, 14 Mar 2011 21:29:33 GMTEvil Kraig Fhttp://www.sqlservercentral.com/Forums/Topic1075989-203-1.aspx[quote][b]Craig Farrell (3/14/2011)[/b][hr]A bit ignorant here, what's an MLM?[/quote]Multi-Level Marketing. Think, Amway, ACN, Avon, and a whole host of other companies similar in nature.Mon, 14 Mar 2011 20:55:29 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1075989-203-1.aspxA bit ignorant here, what's an MLM?Mon, 14 Mar 2011 16:47:33 GMTEvil Kraig Fhttp://www.sqlservercentral.com/Forums/Topic1075989-203-1.aspx[quote][b]Michael Meierruth (3/14/2011)[/b][hr]What sort of hierarchy breaths and depths are people running into in the real world?Numbers please!Thus for a large telephone company I deal with, I have a table of 300 nodes at 5 levels.Each terminal node deals with one or more fairly high level objects such as a company project above a certain cost.Of course, starting at each such a node you typically start a new tree with lots of details - typically 50 more nodes at 3 levels.But the main tree and sub-trees are dealt with separately.And this is where the fun starts.So what sort of hierarchies are people dealing with in the real world?Is it really zillions of nodes?Or is it a couple of hundred most of the time?[/quote]MLM companies can have memberships of millions of nodes several hundred levels deep and very wide fanouts in the dozens or more.Other than such monsters, the HeirarchyID will very easily accomodate some very large hierarchies. I can't vouch for the performance yet but the rumor mill on the internet seems to indicate that it's a bit slow.Mon, 14 Mar 2011 16:28:00 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1075989-203-1.aspxWhat sort of hierarchy breaths and depths are people running into in the real world?Numbers please!Thus for a large telephone company I deal with, I have a table of 300 nodes at 5 levels.Each terminal node deals with one or more fairly high level objects such as a company project above a certain cost.Of course, starting at each such a node you typically start a new tree with lots of details - typically 50 more nodes at 3 levels.But the main tree and sub-trees are dealt with separately.And this is where the fun starts.So what sort of hierarchies are people dealing with in the real world?Is it really zillions of nodes?Or is it a couple of hundred most of the time?Mon, 14 Mar 2011 16:15:58 GMTMichael Meierruthhttp://www.sqlservercentral.com/Forums/Topic1075989-203-1.aspx[quote][b]CirquedeSQLeil (3/14/2011)[/b][hr][quote][b]Jeff Moden (3/14/2011)[/b][hr][quote][b]CirquedeSQLeil (3/14/2011)[/b][hr][quote][b]Jeff Moden (3/10/2011)[/b][hr][quote][b]CirquedeSQLeil (3/10/2011)[/b][hr]Nice article Jeff. I have used a very similar method to this for very large hierarchies. Nice examples and well explained and illustrated.[/quote]Thank you, Jason. Since you're working with very large hierarchies, have you or are you using the HierarchyID datatype at all?[/quote]No, we don't use the HierarchyID datatype - not at all.[/quote]Sorry for peppering you with question but is there a particular reason or is it just that there's no reason to convert legacy code?[/quote]Two-fold. In our testing it seemed that the heirarchyid actually slowed us down a bit. Second - the change would require client sign-off/buy-in. If a client really wanted it, the legacy code could be changed - but they would need to work it into the release schedule and prioritize it. Other things always seem more important and the performance we see for our trees is really good (still need to see your method you were working on for the PASS preso).[/quote]To be honest, I've not tested for performance using HierarchyID's but the internet seems somewhat littered with similar complaints of performance. In the short term, I can make the same offer to you that I did Sean above. Send me an email if you'd like.Mon, 14 Mar 2011 13:23:48 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1075989-203-1.aspx[quote][b]sean-495500 (3/14/2011)[/b][hr]Jeff, when you are next sitting down and giving some thought to your next episode in this drama, maybe you could consider some of these considerations. I know that our team would appreciate it immensely.[/quote]The truth be told, I'm working on a large article with MLM's and other large hierarchies in mind. For what the new methods I have instituted are, I believe it will easily satisfy your needs and pretty much blow away methods that use the HierarchyID data.I'm certainly not ready to publish the article but I do have a test bed which resolves the 4 most request questions of a Uni-level MLM for a million member hierarchy in less than 2 minutes in a very demonstrable fashion. If you care to, please send me an email through this site. I'll also have to state that since this would be direct work, a box of some very nice steaks may be in order. :-)Mon, 14 Mar 2011 13:19:58 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1075989-203-1.aspxOur dev team recently needed to develop a complete solution for a Multi-Level Network Marketing company. They needed to provide a set of reports and views for all of their members based on the signed-in user's level.Having been in this end of the woods before I immediately started using the CTEs with an adjacency model. The problem that we started to experience in our planning and testing was that we had no idea how deep an international networking marketing company can actually go. The result is that the number of CTEs and related views and related table-valued functions started becoming awkward to manage.After a few days of serious head-butting with the traditional hierarchy solutions I eventually caved in and started to investigate the new HierarchyID data type - and was pleasantly surprised. In essence it is a replacement for a lot of our own code to find the parent records, find the full path of the current record and a host of other things.What I liked was the simplification of the code and the fact that the HierarchyID data type allowed for a clustered index, a depth-wise or breadth-wise index and it allowed for reasonably quick extraction of data.We have not yet done extensive performance testing on the solution and are still concerned about the fact that the maximum size for the HierarchyID column is 982 bytes. Now 982 sounds VERY big, but it may not be big enough to cope with a multinational multi-level network marketing solution that has been running for about 3 years and growing exponentially.I'm guessing that SQL 2008R2 has optimised this datatype so that it would provide better all round performance in this type of solution to the traditional CTE approach, but I would love to hear "Truth from the field" accounts.Jeff, when you are next sitting down and giving some thought to your next episode in this drama, maybe you could consider some of these considerations. I know that our team would appreciate it immensely.Mon, 14 Mar 2011 12:16:00 GMTSeanAchimhttp://www.sqlservercentral.com/Forums/Topic1075989-203-1.aspx[quote][b]Jeff Moden (3/14/2011)[/b][hr][quote][b]CirquedeSQLeil (3/14/2011)[/b][hr][quote][b]Jeff Moden (3/10/2011)[/b][hr][quote][b]CirquedeSQLeil (3/10/2011)[/b][hr]Nice article Jeff. I have used a very similar method to this for very large hierarchies. Nice examples and well explained and illustrated.[/quote]Thank you, Jason. Since you're working with very large hierarchies, have you or are you using the HierarchyID datatype at all?[/quote]No, we don't use the HierarchyID datatype - not at all.[/quote]Sorry for peppering you with question but is there a particular reason or is it just that there's no reason to convert legacy code?[/quote]Two-fold. In our testing it seemed that the heirarchyid actually slowed us down a bit. Second - the change would require client sign-off/buy-in. If a client really wanted it, the legacy code could be changed - but they would need to work it into the release schedule and prioritize it. Other things always seem more important and the performance we see for our trees is really good (still need to see your method you were working on for the PASS preso).Mon, 14 Mar 2011 11:13:03 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic1075989-203-1.aspx[quote][b]CirquedeSQLeil (3/14/2011)[/b][hr][quote][b]Jeff Moden (3/10/2011)[/b][hr][quote][b]CirquedeSQLeil (3/10/2011)[/b][hr]Nice article Jeff. I have used a very similar method to this for very large hierarchies. Nice examples and well explained and illustrated.[/quote]Thank you, Jason. Since you're working with very large hierarchies, have you or are you using the HierarchyID datatype at all?[/quote]No, we don't use the HierarchyID datatype - not at all.[/quote]Sorry for peppering you with question but is there a particular reason or is it just that there's no reason to convert legacy code?Mon, 14 Mar 2011 11:03:32 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1075989-203-1.aspx[quote][b]Jeff Moden (3/10/2011)[/b][hr][quote][b]CirquedeSQLeil (3/10/2011)[/b][hr]Nice article Jeff. I have used a very similar method to this for very large hierarchies. Nice examples and well explained and illustrated.[/quote]Thank you, Jason. Since you're working with very large hierarchies, have you or are you using the HierarchyID datatype at all?[/quote]No, we don't use the HierarchyID datatype - not at all.Mon, 14 Mar 2011 09:47:50 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic1075989-203-1.aspxThanks, Peter. I've brought all guns to bear on the new splitter article so that shouldn't be very much longer.As a side bar... MAN! I've got a lot to do in the very near future. To coin a phrase, "It's no longer a matter of how many irons I have in the fire. It's now a matter of how many fires I have irons in." :-PMon, 14 Mar 2011 08:58:01 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1075989-203-1.aspxJeff, I can't wait for your new articles.The CTE and sorting aproach I had to deal with a few times and isn't that new to me, but nested sets and such I still have to dig into. Your articles always spin off a nice discussion with new things to learn even on familiar subjecst.Also looking forward to that new string splitting article you were talking about not to long ago.Cheers!Mon, 14 Mar 2011 04:48:13 GMTpeter-757102http://www.sqlservercentral.com/Forums/Topic1075989-203-1.aspxVery cool compliment. :blush: Thanks Mishaluba!I aim to please. I sometimes miss but I'm always aiming. :-DSat, 12 Mar 2011 19:30:02 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1075989-203-1.aspxJeff is the best as always! Always look forward to his "SQL Spackle" articles. Thank you!Sat, 12 Mar 2011 12:06:50 GMTmishalubahttp://www.sqlservercentral.com/Forums/Topic1075989-203-1.aspx[quote][b]JunkIt (3/11/2011)[/b][hr]In its implementation of recursive CTEs, DB2 has a "Search Depth First" option that can be specified to return rows in this "drill down" fashion. I've been looking for a way to emulate this behavior in SQL Server (without using Hierarchy IDs or a CLR table function) and now I've found it!Thanks for this article.[/quote]Very cool! Thank you for the feedback.I do have to say again, this is a "quick'n'dirty" method to get someone out of the woods. The use of "Nested Set" technology will be much higher in performance even for huge hierarchies. I don't know if you can wait for it but I'm currently writing an article on how to very quickly make the conversion which can be permanent or just long enough for a short query like the one in this article.Fri, 11 Mar 2011 09:29:48 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1075989-203-1.aspxIn its implementation of recursive CTEs, DB2 has a "Search Depth First" option that can be specified to return rows in this "drill down" fashion. I've been looking for a way to emulate this behavior in SQL Server (without using Hierarchy IDs or a CLR table function) and now I've found it!Thanks for this article.Fri, 11 Mar 2011 07:32:38 GMTJunkIthttp://www.sqlservercentral.com/Forums/Topic1075989-203-1.aspx[quote][b]sean-495500 (3/10/2011)[/b][hr]Nice article Jeff. I see that you promised a more in-depth/advanced discussion within the body of your article. Are you going to be discussing the HierarchyID data type in that article?I think that this demonstration piece of code shows the value of CTEs - and is great for small HR type applications that do not required scalability, but there is huge benefit to using the HierarchyID data type as an additional column or even to replace the Primary Key value with.Perhaps your next article could explore these alternatives?[/quote]My apologies... I missed this post earlier. I didn't really want to cover anything about the HierarchyID datatype, how to use it, or a performance comparison but I would be remiss if I left it out.As a side bar, the method I've shown in this "SQL Spackle" article works quite well (absolutely not as good as "Nested Sets") even on huge hierarchical databases especially if one indexes the parent/child relationship properly. As I said before, though, I can't really address the performance of the HierarchyID datatype or it's SQLCLR based functionality because I've simply not used it. I tend to take the time to convert to the "Nested Set" method. I will, however, be sure to test all of that. Thanks again for the feedback. :-)Thu, 10 Mar 2011 19:09:37 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1075989-203-1.aspx[quote][b]CirquedeSQLeil (3/10/2011)[/b][hr]Nice article Jeff. I have used a very similar method to this for very large hierarchies. Nice examples and well explained and illustrated.[/quote]Thank you, Jason. Since you're working with very large hierarchies, have you or are you using the HierarchyID datatype at all?Thu, 10 Mar 2011 19:00:07 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1075989-203-1.aspx[quote][b]gregg_dn (3/10/2011)[/b][hr]Great article Jeff.[/quote]Thanks for stopping by, Gregg. I appreciate the compliment.Thu, 10 Mar 2011 18:56:02 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1075989-203-1.aspx[quote][b]TheSQLGuru (3/10/2011)[/b][hr]I use 'generated' TSQL like that all the time but never thought to make the leap and intermix straight/generated TSQL in a CTE like that. Oh the possibilities!! Nicely done Jeff, as usual![/quote]I wish I could take the credit for such an ingenious thing but the "generated" TSQL that I believe you're citing in the article isn't actually "generated". It's a mistake of the "prettifier" this site has that inappropriately colored some of my code as if it were a string. I'll try to fix it so that folks are not misled into thinking that I did some sort of magic here. :blush:I do, however, greatly appreciate the feedback.Thu, 10 Mar 2011 18:53:00 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1075989-203-1.aspx[quote][b]CELKO (3/10/2011)[/b][hr]Jeff, GSquare, et alI am doing the second edition of TREES & HIERARCHIES this year. If you have stuff that ought to be in the book, plese send it to me ASAP. Think of the fame, the glory, the books and beer that comes with it!For the record and the other readers:To find the level of each emp_name, so you can print the tree as an indented listing. SELECT T1.node, SUM(CASE WHEN T2.lft <= T1.lft THEN 1 ELSE 0 END + CASE WHEN T2.rgt < T1.lft THEN -1 ELSE 0 END) AS lvl FROM Tree AS T1, Tree AS T2WHERE T2.lft <= T1.lftGROUP BY T1.node;An untested version of this using OLAP functions might be better able to use the ordering. Ut will not work in T-SQL becasue we don't have the RANGE sub-clause yet. SELECT T1.node, SUM(CASE WHEN T2.lft <= T1.lft THEN 1 ELSE 0 END + CASE WHEN T2.rgt < T1.lft THEN -1 ELSE 0 END) OVER (ORDER BY T1.lft RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS lvl FROM Tree AS T1, Tree AS T2 WHERE T2.lft <= T1.lft;[/quote]For levels in nested sets, I've usually just found it easier to store that in the table, generated when you generate the boundary values. The whole idea of nested sets hierarchies, in my opinion, is to hard-store the values you'll need so that queries require the absolute minimum of runtime math. Sort of like a warehouse that way.[i]Edit: I just took a closer look at that code, and it looks like a triangular join (classic "running total" issue). Correct me if I'm wrong on that, Joe. If it is what it looks like, it'll have similar performance-killing properties to any other triangular join, and you're probably better off going to a simple adjacency model and recursive CTE, than using that. Recursion is less expensive in most cases than triangles are.[/i]As for data on hierarchies that you can include, you've seen my hierarchies article on this site and the discussions about it (you replied in the discussion). So long as you cite it, and Red Gate/Steve have no issues with it, you're free to use whatever you like from that. I don't publish online unless I expect something to be used by others. (IP protection is a laughable subject these days, in my opinion. Same as privacy. They're both myths.)Thu, 10 Mar 2011 13:37:35 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic1075989-203-1.aspxHeh... Gus beat me to it but he's, apparently, actually done some testing.(Note to self... need performance testing code proofs in the article I'm writing.) :-PThu, 10 Mar 2011 13:24:34 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1075989-203-1.aspx[quote][b]sean-495500 (3/10/2011)[/b][hr][b]Using HierarchyID Datatype with this solution[/b]If added a new column into the table called OrganisationLevel with a HierarchyID datatype, then updated this column with the Hierarchy data that you generated through the T-SQL statement ('/1/1/2....'), then you will end up with a table of data that stores the hierarchy info in what amounts to a hex value set. Obviously you would have to include the Cast('/1/2/3/....' as HierarchyID) conversion function into your update statement to make sure that the hierarchy info that forms your path is then correctly stored.[u][b]NOW[/b][/u] you can have some fun, because your entire CTE statement can now be simply replaced by the Select field1,field2,field3..., OrganisationLevel.ToString() from YourTable.That translates into a single line of code as opposed to your very elegant, but very code intensive CTE.Also, to now get to figure out who the manager of any given record is as simple as Select OrganisationLevel.GetAncestor(1) from TABLENAME and getting higher management levels is OrganisationLevel.GetAncestor(2) etc. These values will obviously return the Hex values which is the natural state of the HierarchyID datatype, unless your use the ToString() function as a suffix (OrganisationLevel.GetAncestor(1).ToString())Just watch out that all of the HierarchyID functions are ALL CASE SENSITIVE.Using the HierarchyID datatype is FAR more efficient than using the CTE, especially since you can create both Depth- and Breadth-based indexes. You can even choose to use the HierarchyID as your primary Key value and create your clustered index on this column, yielding far superior performance than what can be achieved from the CTE and adjacency model.[/quote]Absolutely correct... I'll remind everyone, though, that this article is an "SQL Spackle" article and not meant to be a complete solution. I'll also remind folks that the "Nested Set" solution (not covered in this article) has been touted to be head and shoulder's above even the use of the HierarchyID datatype. I'm currently in the process of testing that claim so I can't yet say whether that's right or wrong... only that it's a frequently touted claim. :-)Thu, 10 Mar 2011 13:21:17 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1075989-203-1.aspx[quote][b]sean-495500 (3/10/2011)[/b][hr][b]Using HierarchyID Datatype with this solution[/b]If added a new column into the table called OrganisationLevel with a HierarchyID datatype, then updated this column with the Hierarchy data that you generated through the T-SQL statement ('/1/1/2....'), then you will end up with a table of data that stores the hierarchy info in what amounts to a hex value set. Obviously you would have to include the Cast('/1/2/3/....' as HierarchyID) conversion function into your update statement to make sure that the hierarchy info that forms your path is then correctly stored.[u][b]NOW[/b][/u] you can have some fun, because your entire CTE statement can now be simply replaced by the Select field1,field2,field3..., OrganisationLevel.ToString() from YourTable.That translates into a single line of code as opposed to your very elegant, but very code intensive CTE.Also, to now get to figure out who the manager of any given record is as simple as Select OrganisationLevel.GetAncestor(1) from TABLENAME and getting higher management levels is OrganisationLevel.GetAncestor(2) etc. These values will obviously return the Hex values which is the natural state of the HierarchyID datatype, unless your use the ToString() function as a suffix (OrganisationLevel.GetAncestor(1).ToString())Just watch out that all of the HierarchyID functions are ALL CASE SENSITIVE.Using the HierarchyID datatype is FAR more efficient than using the CTE, especially since you can create both Depth- and Breadth-based indexes. You can even choose to use the HierarchyID as your primary Key value and create your clustered index on this column, yielding far superior performance than what can be achieved from the CTE and adjacency model.[/quote]I've done a bunch of testing, what I find is that the hierarchyID datatype is slower to select than nested sets, and slower to update/insert/delete than adjacency. It's okay as a compromise, but I've found that a hybrid hierarchy works better for just about any actual application (nested sets and adjacency in the same table; generate the sets from the adjacency and use that to query, then use adjacency for simple update/insert).Have you done performance testing on hierarchyID that contradicts that?Thu, 10 Mar 2011 12:43:48 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic1075989-203-1.aspx[b]Using HierarchyID Datatype with this solution[/b]If added a new column into the table called OrganisationLevel with a HierarchyID datatype, then updated this column with the Hierarchy data that you generated through the T-SQL statement ('/1/1/2....'), then you will end up with a table of data that stores the hierarchy info in what amounts to a hex value set. Obviously you would have to include the Cast('/1/2/3/....' as HierarchyID) conversion function into your update statement to make sure that the hierarchy info that forms your path is then correctly stored.[u][b]NOW[/b][/u] you can have some fun, because your entire CTE statement can now be simply replaced by the Select field1,field2,field3..., OrganisationLevel.ToString() from YourTable.That translates into a single line of code as opposed to your very elegant, but very code intensive CTE.Also, to now get to figure out who the manager of any given record is as simple as Select OrganisationLevel.GetAncestor(1) from TABLENAME and getting higher management levels is OrganisationLevel.GetAncestor(2) etc. These values will obviously return the Hex values which is the natural state of the HierarchyID datatype, unless your use the ToString() function as a suffix (OrganisationLevel.GetAncestor(1).ToString())Just watch out that all of the HierarchyID functions are ALL CASE SENSITIVE.Using the HierarchyID datatype is FAR more efficient than using the CTE, especially since you can create both Depth- and Breadth-based indexes. You can even choose to use the HierarchyID as your primary Key value and create your clustered index on this column, yielding far superior performance than what can be achieved from the CTE and adjacency model.Thu, 10 Mar 2011 11:54:15 GMTSeanAchimhttp://www.sqlservercentral.com/Forums/Topic1075989-203-1.aspx[quote][b]CELKO (3/10/2011)[/b][hr]Jeff, GSquare, et alI am doing the second edition of TREES & HIERARCHIES this year. If you have stuff that ought to be in the book, plese send it to me ASAP. Think of the fame, the glory, the books and beer that comes with it!For the record and the other readers:To find the level of each emp_name, so you can print the tree as an indented listing. SELECT T1.node, SUM(CASE WHEN T2.lft <= T1.lft THEN 1 ELSE 0 END + CASE WHEN T2.rgt < T1.lft THEN -1 ELSE 0 END) AS lvl FROM Tree AS T1, Tree AS T2WHERE T2.lft <= T1.lftGROUP BY T1.node;An untested version of this using OLAP functions might be better able to use the ordering. Ut will not work in T-SQL becasue we don't have the RANGE sub-clause yet. SELECT T1.node, SUM(CASE WHEN T2.lft <= T1.lft THEN 1 ELSE 0 END + CASE WHEN T2.rgt < T1.lft THEN -1 ELSE 0 END) OVER (ORDER BY T1.lft RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS lvl FROM Tree AS T1, Tree AS T2 WHERE T2.lft <= T1.lft;[/quote]Although I certainly appreciate the code and the polite nature of your post, you need to explain to people that both methods are using a "Nested Set" hierarchy and really has nothing to do with the article this discussion is actually about.Thu, 10 Mar 2011 11:29:23 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1075989-203-1.aspx[quote][b]CELKO (3/10/2011)[/b][hr]I am doing the second edition of TREES & HIERARCHIES this year. If you have stuff that ought to be in the book, plese send it to me ASAP. [/quote]You and I started to have this discussion before, Joe. You and I pretty much disagree when it comes to how to talk to/with people and just about everything T-SQL except that the data should be normalized. ;-) Heh... you even called me a "baby killer" in one of your famous rants years ago (even though it was your code that didn't work). :-D Hell, I even disconnected from you on Linked-In because you're normally such a, ummm... less than considerate poster and your attitude on most of your posts. So let me ask... other than being in a "Joe Celko" book, what's in it for me and what's in it for your readers since I was going to publish the methods I've developed in a month or two anyway? Perhaps if you could explain those things, I might reconsider my previous answer from a month ago from "I'll have to pass" to something more affirmative.Thu, 10 Mar 2011 11:26:02 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1075989-203-1.aspxJeff, GSquare, et alI am doing the second edition of TREES & HIERARCHIES this year. If you have stuff that ought to be in the book, plese send it to me ASAP. Think of the fame, the glory, the books and beer that comes with it!For the record and the other readers:To find the level of each emp_name, so you can print the tree as an indented listing. SELECT T1.node, SUM(CASE WHEN T2.lft <= T1.lft THEN 1 ELSE 0 END + CASE WHEN T2.rgt < T1.lft THEN -1 ELSE 0 END) AS lvl FROM Tree AS T1, Tree AS T2WHERE T2.lft <= T1.lftGROUP BY T1.node;An untested version of this using OLAP functions might be better able to use the ordering. Ut will not work in T-SQL becasue we don't have the RANGE sub-clause yet. SELECT T1.node, SUM(CASE WHEN T2.lft <= T1.lft THEN 1 ELSE 0 END + CASE WHEN T2.rgt < T1.lft THEN -1 ELSE 0 END) OVER (ORDER BY T1.lft RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS lvl FROM Tree AS T1, Tree AS T2 WHERE T2.lft <= T1.lft;Thu, 10 Mar 2011 10:37:04 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic1075989-203-1.aspxNice article Jeff. I see that you promised a more in-depth/advanced discussion within the body of your article. Are you going to be discussing the HierarchyID data type in that article?I think that this demonstration piece of code shows the value of CTEs - and is great for small HR type applications that do not required scalability, but there is huge benefit to using the HierarchyID data type as an additional column or even to replace the Primary Key value with.Perhaps your next article could explore these alternatives?Thu, 10 Mar 2011 10:24:28 GMTSeanAchimhttp://www.sqlservercentral.com/Forums/Topic1075989-203-1.aspxNice article Jeff. I have used a very similar method to this for very large hierarchies. Nice examples and well explained and illustrated.Thu, 10 Mar 2011 10:16:43 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic1075989-203-1.aspxGreat article Jeff.Thu, 10 Mar 2011 09:52:47 GMTgregg_dnhttp://www.sqlservercentral.com/Forums/Topic1075989-203-1.aspx[quote][b]Jeff Moden (3/10/2011)[/b][hr][quote][b]GSquared (3/10/2011)[/b][hr]I've used a variation on this solution for sorting before, and found that it works best if you pad numerical values with leading zeroes to a fixed length, before sorting them as character data.[/quote]Hi Gus,Thanks for stopping by. I agree... even better is the method of converting numeric ID data to BINARY(4) data and concatenating that information into a VARBINARY() column... sans slashes, of course.[/quote]Yep.Thu, 10 Mar 2011 09:19:18 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic1075989-203-1.aspx[quote][b]Jeff Moden (3/10/2011)[/b][hr][quote][b]GSquared (3/10/2011)[/b][hr]I've got a two-step script that can generate a nested sets "path" from an adjacency hierarchy, turning it into a nested sets hierarchy, using Cross Apply and a ranking function.[/quote]Is that method in the good article you wrote on hierarchies, Gus?[/quote]Nope. Developed after that.Thu, 10 Mar 2011 09:18:57 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic1075989-203-1.aspxI use 'generated' TSQL like that all the time but never thought to make the leap and intermix straight/generated TSQL in a CTE like that. Oh the possibilities!! Nicely done Jeff, as usual!Thu, 10 Mar 2011 09:16:09 GMTTheSQLGuruhttp://www.sqlservercentral.com/Forums/Topic1075989-203-1.aspx[quote][b]GSquared (3/10/2011)[/b][hr]I've got a two-step script that can generate a nested sets "path" from an adjacency hierarchy, turning it into a nested sets hierarchy, using Cross Apply and a ranking function.[/quote]Is that method in the good article you wrote on hierarchies, Gus?Thu, 10 Mar 2011 08:01:43 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1075989-203-1.aspx[quote][b]Jeff Moden (3/10/2011)[/b][hr][quote][b]Wesley Brown (3/10/2011)[/b][hr]I prefer to use a two column approach to build a tree instead of the single column style that you have to split, it doesn't scale well. For smaller stuff like this HR representation it should be OK. Trees and Hierarchies in SQL from Joe Celko is one of my all time favorite books! Good article!your brother,Phil McCrevice[/quote]If, by "two column approach", you mean "Nested Sets", I absolutely agree. In the larger article I'm writing on hierarchies, I'll actually demonstrate a new method for converting an "Adjaceny List" to a "Nested Set" that I think you'll like especially since it gets away from the RBAR of a "push stack" to build the "Nested Set". Ben-Gan also has an alternate method for doing the same thing.You'll also like the "warehouse" table that I'll build in the coming article which you might prefer to a "Nested Set" because the "warehouse" table contains preaggregated answers for the 4 of the more common hierarchical lookups that people seem to do on a regular basis.[/quote]I've got a two-step script that can generate a nested sets "path" from an adjacency hierarchy, turning it into a nested sets hierarchy, using Cross Apply and a ranking function. Single query, and much more efficient than the method Joe originally proposed (but also uses features he didn't have when he originally wrote the article).I've found you can also short-circuit the conversion and get really good performance using the ASCII table and a binary colation for your set headers and ranges instead of numeric values. You don't need the binary colation for smaller hierarchies, but it comes in handy for bigger/complex ones with lots of top levels. (This method takes advantage of the fact that you can, in the recursive part of the CTE, concat a string together based on level and row_number within the level, and that "AAAB" is between "AAA" and "AAB" in a string sort. Much simpler than using integers for the ranges.)The string-range version is a bit of a pain to set up in the first place (making sure all top level and sub-level strings are guaranteed unique out of a limited dataset of ASCII characters that can be sorted on), but works nicely once it's in place.I'm interested in seeing how your article solves it.Thu, 10 Mar 2011 07:52:43 GMTGSquared