InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 18 May 2013 04:03:06 GMT20http://www.sqlservercentral.com/Forums/Topic644681-1312-1.aspxI know I am a little late here but, Great article Gus. FYI - the to Celko's article ([url=http://www.intelligententerprise.com/001020/celko.jhtml]http://www.intelligententerprise.com/001020/celko.jhtml[/url].) is broke ;-)Wed, 06 Mar 2013 15:59:02 GMTAlan.Bhttp://www.sqlservercentral.com/Forums/Topic644681-1312-1.aspxThanks, Gus.Sat, 29 Jan 2011 02:09:50 GMTamenjonathanhttp://www.sqlservercentral.com/Forums/Topic644681-1312-1.aspx[quote][b]amenjonathan (1/28/2011)[/b][hr]Just wanted to post back to this thread that I finished my blog post on handling hierarchies in slowly changing dimensions. If I've misquoted anyone (especially Gus) please let me know. Also any feedback if someone knows of a better / faster way.[url=http://amenjonathan.wordpress.com/2011/01/27/updating-a-slowly-changing-dimension-with-a-recursive-relationship-on-its-key/]SCD with hierarchy in key[/url][/quote]I read the post, and it looks like it makes sense and would work. Definitely well-written too.Fri, 28 Jan 2011 20:58:54 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic644681-1312-1.aspxJust wanted to post back to this thread that I finished my blog post on handling hierarchies in slowly changing dimensions. If I've misquoted anyone (especially Gus) please let me know. Also any feedback if someone knows of a better / faster way.[url=http://amenjonathan.wordpress.com/2011/01/27/updating-a-slowly-changing-dimension-with-a-recursive-relationship-on-its-key/]SCD with hierarchy in key[/url]Fri, 28 Jan 2011 13:23:06 GMTamenjonathanhttp://www.sqlservercentral.com/Forums/Topic644681-1312-1.aspxThanks Gus.Thu, 20 Jan 2011 13:42:19 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic644681-1312-1.aspxI don't have access to that code currently, since it's for a prior employer. However, if I remember it correctly, it ended up using the TopParentID mentioned in the article to first find a simple count by that column, did a "running total" type calculation on that (I use a CLR function for that, blindingly fast) to get top level range start and stop values. All of that was very, very fast, like milliseconds. I think it then repeated that for each level till it got zero for @@rowcount, but the lower levels weren't as fast because they had to actually crawl the hierarchy to get the number of nodes beneath each, instead of just a count on TopParentID.I tried the update method mentioned in Joe's article and found that it was WAY too slow for an in-use database. My update solution was more complex, but much, much faster.I could improve the process immensely with the difference between what I know now and what I knew when I built it, but that's pretty much true of any code I wrote more than about a month ago. Just some simple Cross Apply inline queries would make the thing much more efficient.The 2,700 rows was for one hierarchy within a multi-million row table. 11 seconds to resolve anything on a 2,700-row table would imply that I was running it on, maybe a 286 CPU with 2 Meg of RAM? TSR-80? Timex/Sinclair 1000? Not sure how far back I'd have to go to get that bad of performance, even on an adjacency crawl. If I remember correctly, the table had somewhere around 2-million total rows, and 2,700 nodes, 6 levels deep, was the biggest single hierarchy within it.Wed, 19 Jan 2011 06:37:07 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic644681-1312-1.aspx[quote]From the article...[hr]The speed difference and IO difference can be significant on the two. For example, [b]I have a hierarchy in one of my databases with 2,700 nodes in it, going up to six levels deep. If someone at the top of that hierarchy signs in, it takes 11 seconds for my server to resolve the whole hierarchy[/b] and determine what data that person has access to (this is a security access hierarchy that controls much of what is displayed to customers on a web site). That’s using the adjacency model. Using a nested sets table, that same hierarchy takes less than 1 millisecond. (This isn’t the one with multiple parents. Different company.)If this same database didn’t have a lot of updates to the hierarchies, I’d definitely use a straight-up nested sets hierarchy, and have much faster web pages. But it does have a lot of updates, sometimes several per minute, sometimes several at the same time. [font="Arial Black"]Each nested sets rebuild takes about 20-30 seconds to finish[/font], and locks the table pretty aggressively while it’s running.[/quote]Hi Gus,I've had reason to revisit this article and the paragraphs above caught my eye, especially the two emphasized areas.With that in mind, which method are you using to convert the Adjacency List to a Nested Set Model? Is it the familiar "push stack" method from Joe Celko's book/article postings? And was it for the ~2700 rows you mentioned?Thanks, Gus.Tue, 18 Jan 2011 18:56:16 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic644681-1312-1.aspx[quote][b]mishaluba (12/14/2010)[/b][hr]Thank you for the responses, but I was thinking more along the lines of slowly-chaning dimension, rather then audit/logging. In one of the earlier comments Jeff Storm showed a portion of his solution, which is closer to what I am after. I would like for the application to be able to "look" at the same data using different hierarchy structures (Hierarchy A, which existed at time X and Hierarchy B, which existed at time Y) and all of them have to be equally accessible in the same set of tables.Thank you![/quote]I actually just got done building this exact solution yesterday. I have a parent child relationship in our customer (accounts) table, and it's using SCD. I wish I had time to explain how I solved this. ... It's going to have to wait until I can make a blog on it. Maybe I can do that this weekend. Right now I don't have time to explain it. :(I'll try and post back here if I can blog on it this weekend...Thu, 13 Jan 2011 10:02:37 GMTamenjonathanhttp://www.sqlservercentral.com/Forums/Topic644681-1312-1.aspx[quote][b]mishaluba (12/14/2010)[/b][hr]Thank you for the responses, but I was thinking more along the lines of slowly-chaning dimension, rather then audit/logging. In one of the earlier comments Jeff Storm showed a portion of his solution, which is closer to what I am after. I would like for the application to be able to "look" at the same data using different hierarchy structures (Hierarchy A, which existed at time X and Hierarchy B, which existed at time Y) and all of them have to be equally accessible in the same set of tables.Thank you![/quote]The usual solution for that is adding effective dates to the table, and using those in your queries.That often has a significant impact on performance, and always makes coding a bit more complex, but it does work.Tue, 14 Dec 2010 14:11:03 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic644681-1312-1.aspxI'm the first to admit that I'm naive and uninformed, but it sounds to me as if it's basically an issue of you:1) deciding what data you need to store, including any "related" data you may need2) deciding what DML events should trigger your storing the old/previous valuesFrom there it's fairly straightforward to decide on a method to copy the old data into a separate table/column/etc. No?I'm probably missing something.Tue, 14 Dec 2010 13:27:29 GMTautoexcrementhttp://www.sqlservercentral.com/Forums/Topic644681-1312-1.aspxThank you for the responses, but I was thinking more along the lines of slowly-chaning dimension, rather then audit/logging. In one of the earlier comments Jeff Storm showed a portion of his solution, which is closer to what I am after. I would like for the application to be able to "look" at the same data using different hierarchy structures (Hierarchy A, which existed at time X and Hierarchy B, which existed at time Y) and all of them have to be equally accessible in the same set of tables.Thank you!Tue, 14 Dec 2010 13:17:32 GMTmishalubahttp://www.sqlservercentral.com/Forums/Topic644681-1312-1.aspx[quote][b]mishaluba (12/11/2010)[/b][hr]Thank you for the article! We are getting started on a project requiring us to maintain hierarchies and I found the information here very useful. What's missing though is the ability to track hierarchies and their changes over time. What if I need to know Parent-Child relationships as they were at any point in time? Any recommendations?Thank you![/quote]My other main article for SQL Server Central is on audit trails and logging. It's at [url]http://www.sqlservercentral.com/articles/Auditing/63247/[/url], with part 2 at [url]http://www.sqlservercentral.com/articles/Auditing/63248/[/url].[quote][b]autoexcrement (12/11/2010)[/b][hr]Could you just use change data capture, or any traditional type of trigger-based auditing to track changes to your tables as needed?[/quote]Keep in mind that, with change-data-capture, certain types of table DDL will be blocked, in the same manner as when you have replication on a table. Triggers or passive logging don't have that issue, if they're set up correctly.Mon, 13 Dec 2010 06:50:00 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic644681-1312-1.aspx[quote]What if I need to know Parent-Child relationships as they were at any point in time?[/quote]Could you just use change data capture, or any traditional type of trigger-based auditing to track changes to your tables as needed?Sat, 11 Dec 2010 11:48:28 GMTautoexcrementhttp://www.sqlservercentral.com/Forums/Topic644681-1312-1.aspxThank you for the article! We are getting started on a project requiring us to maintain hierarchies and I found the information here very useful. What's missing though is the ability to track hierarchies and their changes over time. What if I need to know Parent-Child relationships as they were at any point in time? Any recommendations?Thank you!Sat, 11 Dec 2010 10:51:37 GMTmishalubahttp://www.sqlservercentral.com/Forums/Topic644681-1312-1.aspx[quote][b]Zeev Kazhdan (12/10/2010)[/b][hr]Interesting article, but I have a question, which hopefully will save me a lot of hours:In Oracle 10 I do it in one select statement, using START WITH and CONNECT BY.Now, migrating one of my customers to SQL 2008, is there anything close to it in SQL Server?Thanks in advance[/quote]The HierarchyID data type has a lot of that kind of functionality. I built this solution in SQL 2000, and then in SQL 2005, where that wasn't available.I've found that nested sets hierarchies still perform much better (in selecting) than the hierarchy ID data type does. Updates are still fastest in adjacency hierarchies, since they usually only involve one row. Deletion speed is fastest in nested sets, second fastest in adjacency, and slowest in hierarchy ID. Additions are fastest in either adjacency, or in a padded nested sets hierarchy (equally fast in either), except in cases where the nested sets version requires resizing or moving any ranges, and are slowest in a hierarchy ID, except when adding one row to the bottom of a chain.So, even in SQL 2008, consider a few things before just going with the hierarchy ID data type, instead of nested sets (which is best if you can get past the issues with frequent changes), or a hybrid system like this. Adjacency should only be used if you absolutely have to. It's a performance killer except on very small data sets or very shallow hierarchies. (It's hard to argue against it on a family tree, for example, while you're building it. But for just about anything else I've run into, one of the others will be better.)Fri, 10 Dec 2010 12:38:34 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic644681-1312-1.aspx[quote][b]autoexcrement (12/10/2010)[/b][hr]Fascinating article! I have a couple of naive questions about the following bit of G code:[code="sql"] Adjacency (NodeID, ParentID) as -- Adjacency Query (select 0, ID, ParentID from dbo.HierarchyHybrid where ID = @NodeID_in andexists (select* from dbo.HierarchyHybrid h2 where h2.TopParentID = HierarchyHybrid.TopParentID and RangeStart is null)[/code]1. Is it supposed to have the "select 0, " or is that just a typo? It looks like the surrounding code is expecting 2 values there, not 3. If not a typo, I don't understand the "0" part. 2. Why doesn't this work?[code="sql"] Adjacency (NodeID, ParentID) as -- Adjacency Query (select 0, ID, ParentID from dbo.HierarchyHybrid where ID = @NodeID_in and RangeStart is null)[/code]Thanks, regardless of whether you have time to reply.[/quote]The 0 is meant to indicate the row that was from the original parameter value. This comes in handy if you are querying up and down the hierarchy and need to know which one you originally asked for.The "and exists" subquery merely tests that the row requested is in a hierarchy that has a top level. The version you wrote tests that it IS the top level. There's a difference.Fri, 10 Dec 2010 12:32:16 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic644681-1312-1.aspx[quote][b]Craig Farrell (11/19/2010)[/b][hr]Hey Gus,Late to the party, I know, but you referenced this in another thread and I had a curiousity. Could you discuss the 'lazy updater' component a bit more. I'm not necessarily sure I understand how the 'temp range' vs. the 'real range' helps with update issues, since you're still locking and unlocking rows/pages/table using either column.[/quote]The idea is to use Snapshot Isolation. That will allow the asynchronous update to complete without wrecking performance while it runs (which was a flaw in my original implementation). This could also be used in a purely nested sets hierarchy, so that infrequent updates don't interfere with concurrent reads. That's what I was refering to in that other thread.Fri, 10 Dec 2010 12:30:41 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic644681-1312-1.aspxThanks for the article Gus. I'm glad I caught it this time around.Fri, 10 Dec 2010 10:56:47 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic644681-1312-1.aspxInteresting article, but I have a question, which hopefully will save me a lot of hours:In Oracle 10 I do it in one select statement, using START WITH and CONNECT BY.Now, migrating one of my customers to SQL 2008, is there anything close to it in SQL Server?Thanks in advanceFri, 10 Dec 2010 07:14:57 GMTZeev Kazhdanhttp://www.sqlservercentral.com/Forums/Topic644681-1312-1.aspxA very good solution and something that we've implemented in one of our solutions, the only difference is that we also have a RelationshipType table hung off the HeirarchyRelationship table.The code snippet below, is an example of how we set this up to cater for differnt Geographic needs with an organisation.[code="sql"]CREATE TABLE [Common].[Geography] ( [GeographyId] INT IDENTITY (1, 1) NOT NULL, [GeographyCode] NVARCHAR (50) NOT NULL, [GeographyName] NVARCHAR (150) NOT NULL, [Level] NVARCHAR (50) NULL);GOCREATE TABLE [Common].[GeographyRelationship] ( [GeographyRelationshipId] INT IDENTITY (1, 1) NOT NULL, [GeographyId] INT NOT NULL, [ParentGeographyId] INT NOT NULL, [RelationshipTypeId] SMALLINT NOT NULL);GOCREATE TABLE [Common].[RelationshipType] ( [RelationshipTypeId] SMALLINT IDENTITY (1, 1) NOT NULL, [RelationshipTypeName] NVARCHAR (20) NOT NULL, [RelationshipTypeDescription] NVARCHAR (50) NOT NULL );GO[/code]This allows us to create many different variations from a single list of nodes and at a variety of grains, some down only as far as sub-regions (State/County), others as deep as City Street.Using a CTE the SQL is very simular to the Article, with only the addition of a join to the RelationshipType table and filtered on the required Type of Hierarchy.Fri, 10 Dec 2010 04:03:22 GMTJason-299789http://www.sqlservercentral.com/Forums/Topic644681-1312-1.aspxFascinating article! I have a couple of naive questions about the following bit of G code:[code="sql"] Adjacency (NodeID, ParentID) as -- Adjacency Query (select 0, ID, ParentID from dbo.HierarchyHybrid where ID = @NodeID_in andexists (select* from dbo.HierarchyHybrid h2 where h2.TopParentID = HierarchyHybrid.TopParentID and RangeStart is null)[/code]1. Is it supposed to have the "select 0, " or is that just a typo? It looks like the surrounding code is expecting 2 values there, not 3. If not a typo, I don't understand the "0" part. 2. Why doesn't this work?[code="sql"] Adjacency (NodeID, ParentID) as -- Adjacency Query (select 0, ID, ParentID from dbo.HierarchyHybrid where ID = @NodeID_in and RangeStart is null)[/code]Thanks, regardless of whether you have time to reply.Fri, 10 Dec 2010 02:21:47 GMTautoexcrementhttp://www.sqlservercentral.com/Forums/Topic644681-1312-1.aspxHey Gus,Late to the party, I know, but you referenced this in another thread and I had a curiousity. Could you discuss the 'lazy updater' component a bit more. I'm not necessarily sure I understand how the 'temp range' vs. the 'real range' helps with update issues, since you're still locking and unlocking rows/pages/table using either column.Fri, 19 Nov 2010 23:25:06 GMTEvil Kraig Fhttp://www.sqlservercentral.com/Forums/Topic644681-1312-1.aspxVery interesting!Tue, 16 Nov 2010 09:07:47 GMTkenderman3http://www.sqlservercentral.com/Forums/Topic644681-1312-1.aspxniceMon, 18 Oct 2010 02:06:22 GMTasimsubedihttp://www.sqlservercentral.com/Forums/Topic644681-1312-1.aspxThe nested sets model is not as bad for dynamic hierarchies as people think. The tree structure is a table with two integers (lft, rgt) and a foreign key references to the nodes in another table. The rows are pretty short, so you get a lot of them on a data page. If you expect to be adding and removing relationships frequently, then give those pages a higher free space than you normally would for more static data. You can also space the (lft, rgt) pairs apart by steps of 10, 100 or 1000 to allow for easy insertion. Unfortunately, this hurts the algebraic property of the Nested Sets model that makes it handy for front end display of menus. ((rgt-lft+1)/ tells me how many rows are anchored at a given node. The algebraic property is a real boost for developers. When someone asks to see all the items under "Food", I know what to storage allocate on his side of the system. I also know what level of the hierarchy each items is at, so I can plan my sub-menus, tabs or indentation. An Adjacency List model has to build things by traversal, row by row. Recursion in SQL is really a cursor with a loop hidden under the covers. Data integrity is also much easier; try to find cycles and orphans in an Adjacency List model without using procedural code. An Adjacency List model has no natural ordering of siblings (unless you put in another column), so the display of a level of the hierarchy is not guaranteed to be the same. Mon, 11 Oct 2010 11:53:01 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic644681-1312-1.aspxJeff Strom: That's basically the same as what I did, you just have it in two tables where I had it in one, and you have a time-sensitivity that I didn't have. Same concept though. What you've got should be pretty good for what you need.Thu, 29 Jan 2009 07:00:15 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic644681-1312-1.aspxThis article caught my attention because I deal with a related problem. I oversee the field structure for a company. The difference is that the structure is time-based; and a single Area can roll up to multiple parents based on the org type in use. This is primarily so I have the ability to re-state sales based on the structure that was in place on any particular date. The second item is that I am on SQL2K, with a near-term upgrade to 2005 coming, so I could not use any of the new CTE functionality.To do this, I had to use two table, and the "Stack" table requires a rebuild any time the hierarchy changes. (luckily not often)My tables are as follows:[code]create table GeoParent ( GeoParentId int not null identity(1,1), AreaId int not null references dbo.Geo(ID), ParentId int not null references dbo.Geo(ID), DateStart datetime not null, DateEnd datetime not null, OrgTypeId int not null references dbo.GeoOrg(ID))create table GeoStack ( GeoStackId int not null identity(1,1), AreaId int not null references dbo.Geo(ID), Lft int not null, Rgt int not null, Depth int not null, DateStart datetime not null, DateEnd datetime not null, OrgTypeId int not null)[/code]The "Stack" table is built by walking through changes to the Parent table and building the hierachy tree in a temp table, then writing it to the table after each iteration, which is currently performed in an external visual basic DLL.Once in place, I use code similar to danielk1 to get the hierachies:[code]SELECT S2.AreaId FROM GeoStack S1, GeoStack S2WHERE S2.Lft BETWEEN S1.Lft AND S1.Rgt AND @date BETWEEN S1.DateStart AND S1.DateEnd AND @date BETWEEN S2.DateStart AND S1.DateEnd AND S1.AreaId = @tid AND S1.OrgTypeID=@org AND S2.OrgTypeID=@org[/code]Any thoughts?Thanks,StromWed, 28 Jan 2009 18:37:49 GMTJeff Stromhttp://www.sqlservercentral.com/Forums/Topic644681-1312-1.aspx[quote]I have yet to see a case where code portability actually solved anything worthwhile for anyone. Doesn't mean they don't exist, just means I haven't seem them, in my limited experience.[/quote][size="5"][b]++1[/b][/size]Wed, 28 Jan 2009 15:15:34 GMTnoeldhttp://www.sqlservercentral.com/Forums/Topic644681-1312-1.aspxI have to admit, my code is usually about as portable as the Rocky Mountains. In other words, you could move it to another database engine in geologic time spans, but not much faster than that.I also have to admit that I've seen projects that were "completely standard and portable", where someone actually did try to move it to another RDBMS, and spent more time debugging because of partially documented differences, undocumented differences, variations in standards compliance in the RDBMS itself, etc., than they would have spent building the thing over from scratch.I have yet to see a case where code portability actually solved anything worthwhile for anyone. Doesn't mean they don't exist, just means I haven't seem them, in my limited experience.Wed, 28 Jan 2009 14:47:43 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic644681-1312-1.aspxAh... so you're not a batch programmer. And that's the area of the code I was talking about. GUI programmers have all sorts of tools like Hibernate, etc, to write "portable" code... well, at least DB "agnostic" code so far as the GUI is concerned.It would be really interesting to see if any of your DBA's use GETDATE() or any of the other "proprietary" code offerings of SQL Server. I'd have to say that the first time they reference Master.sys.sysColumns, they're probably in violation. :)Wed, 28 Jan 2009 11:37:30 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic644681-1312-1.aspx[quote]Just double checking... You've never used and will never use CONNECT BY until it becomes part of the ISO Standard. And you never used DATEDIFF until it became part of the standard. And you've never used GETDATE() because it's not part of the standard.[/quote]Actually, no. I leave this to the code to handle.The DBAs in all the companies I work for, demand us to follow all ISO standards without deviation.Moreover, the applications I have written, both GUI and Web, are 100% portable to many of the top databases available including: MS SQL, MySQL, DB2, Oracle, and others (tested and implemented).I will not debate the issue as I am not saying I am right nor am I saying I am wrong - it is just one way of solving an issue. The article and others like it are of course another way. In IT, there are many ways to solve a given issue, some better than others.With this said, if performance using a non-ISO standard was significantly improved, the DBAs and other Stake Holders would have to evaluate their thinking and perhaps make an exception, however, as of now, performance is virtually instant and there are no areas to improve, so we will remain with the ISO standards.Why have standards if they are not to be followed and adhered? Sure, they may become outdated. No one is saying they are perfect and even if they were, in six months there will likely be something else better, different, or faster, requiring the standards to be revisited, invoking a new standard.Ahhh - I love IT - it is ever changing and evolving.Take care all.Wed, 28 Jan 2009 11:13:33 GMTdanielk1http://www.sqlservercentral.com/Forums/Topic644681-1312-1.aspx[quote][b]danielk1 (1/28/2009)[/b][hr]I just feel, to tie myself to a DB structure type, like you mentioned using the hierarchcical ID, might limit my application's portability - say, if I want to take it from MS SQL server to MySQL or Oracle or DB2.Doing a small amount of coding in order to remain with ISO standard fields, is in my opinion, making for a far better application.My 2cents.[/quote]Just double checking... You've never used and will never use CONNECT BY until it becomes part of the ISO Standard. And you never used DATEDIFF until it became part of the standard. And you've never used GETDATE() because it's not part of the standard.Here we go again... I can understand that some GUI code might be made to follow ISO standards (heh, depending on which release you're talking about), but since none of the RDBMS vendors are 100% compliant with ISO standards and the very standards themselves tend to lag what's available as powerful extensions to ANSI SQL, I'll just go ahead and start a war by saying that true code portability in the batch world is a myth.Wed, 28 Jan 2009 10:10:12 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic644681-1312-1.aspx[quote][b]ventsislav.mladenov (1/28/2009)[/b][hr]Or you could use CLR store procedures which are very fast in execution and sometimes are faster than T-SQL.http://blog.vmladenov.com/?p=197[/quote]Since you say [i]"Now the fact is that the C# store procedure is faster than T-SQL code. I’m wondering why but this is the result."[/i] in you blog, I'd like to see the T-SQL method code and test data you tested against. I'm always willing to learn something new.Wed, 28 Jan 2009 10:02:19 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic644681-1312-1.aspx[quote][b]danielk1 (1/28/2009)[/b][hr]I just feel, to tie myself to a DB structure type, like you mentioned using the hierarchcical ID, might limit my application's portability - say, if I want to take it from MS SQL server to MySQL or Oracle or DB2.Doing a small amount of coding in order to remain with ISO standard fields, is in my opinion, making for a far better application.[/quote]That is a very good point Daniel and I would definitely consider that if I knew there is a chance the app will be ported to other DB servers, otherwise I would definitely go for hierarchyID (if it proves to be fast enough).This discussion also applies to app architecture - e.g. should we have a separate DAL in the project if we know that we will only use MS Sql Server?Thanks,LukaszWed, 28 Jan 2009 08:52:57 GMTLukkhttp://www.sqlservercentral.com/Forums/Topic644681-1312-1.aspxThanks Noel and Jeff. I appreciate the critique and compliments. :)Wed, 28 Jan 2009 08:29:57 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic644681-1312-1.aspxLukk: definitely agree with you. One of my biggest concerns when I left the job where I built this hybrid hierarchy was whether or not anyone left behind would be able to manage it. I documented the heck out of it, asked them if they were comfortable with it, got reassurances that either they'd figure it out or they'd call me (and pay consulting rates) if they needed help. Not a very good sign, I have to say.Unfortunately, that company didn't have any 2008 servers, and had only vague plans about maybe obtaining some one day in the indeterminate future, so that wasn't really an option. The stuff in this article is moderately complex, but not horribly so, and doesn't depend on a proprietary data type nor on features that most companies don't have yet.Wed, 28 Jan 2009 08:28:29 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic644681-1312-1.aspx[quote][b]ventsislav.mladenov (1/28/2009)[/b][hr]Or you could use CLR store procedures which are very fast in execution and sometimes are faster than T-SQL.http://blog.vmladenov.com/?p=197[/quote]Have you speed-tested the queries on that against nested sets hierarchies?Wed, 28 Jan 2009 08:22:45 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic644681-1312-1.aspxdanielk1: yes, that's a "nested sets" hierarchy.For something like your sample, it's definitely the way to go. It's not likely that "Meat" will ever be moved out of the "Food" category, or that "Beef" will ever move out of the "Meat" sub-category.That works just fine for relatively static hierarchies. No problem.The problem I had to solve was what to do with a volatile hierarchy. In this case, a customer-facing web page that had hierarchical access control.Joe works for company X, and he places an order that will have details on this web page. Joe can see the details, as can various people at X. Simple hierarchy, right? Joe then changes to working with company Y, and now people at X should no longer see his details, but people at Y should. The following week, he moves to Z.At the same time, Bob moves from Z to X, and Sue moves from A to Y, and B adds twelve new people who will all be placing orders and who weren't even in your database.And that's a typical hour for this hierarchy.It's no longer as simple as adding "yellow" by expanding the range for "food". Even that isn't as simple as you might think. What happens in your example if you already have a category "dinnerware" that starts at 19? How do you then add "Vegetable" to "Food"? You can't add 2 to "Food", because now you're going to end up with an overlap with "Dinnerware", which is, of course, wrong. So, you have to redefine "Dinnerware", and everything in it, in order to add "Vegetable" to "Food". Let's say you have to do that kind of thing several times per minute. You need to add "Vegetable" and twenty items in it. At the same time, you need to add four new types of "Meat", six new "Fruit", two more types of "Apple" (under "Fruit"). Also, you're changing who you buy your dinnerware from, and they have different product IDs for plates, cups, glasses, silverware, etc., so you basically have to double the size of that category, but "Dinnerware" has a range that ends at 26 and "Furniture" starts at 27. Suddenly, you have to manage updates that affect nearly every row in your table. The complexity of the update is huge.There are a number of solutions for this. You could add room to each category you add, based on anticipated growth over a reasonable time span. Instead of defining "Food" as 1-12, you define it as 1-1000, even though you only need 1-12 when you first build it. But does "Fruit" get 2-500, or 2-100, or 2-20, in that scheme? Managing the data ranges becomes something that requires a lot more work.A path or adjacency hierarchy handles that kind of situation very, very easily. Want to add a new sub-set to "Food"? Easy, add one row, and its ParentID = "Food". You're done. No other rows to do anything with at all. Now you need to add 100 items under "Dinnerware"? Again, it's a single transaction that simply adds 100 rows to the table, each with the correct ParentID. Milliseconds later, you have your hierarchy fully functional.In other words, there's more to it than what your sample code does. A lot more.Wed, 28 Jan 2009 08:21:21 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic644681-1312-1.aspxI liked the compromised you took when dealing with this problem.I have worked in the past with these issues and my opinion is that it is the "usage" what dictates what's better.- path-based hierarchies are *NOT* slow for many kind of queries but they *ARE* for some others.- Nested-Sets are *NOT* good as you already pointed out for changes BUT also certain queries are "hard" to craft.- The Built-in hierarchy-ID of 2008 is basically a path-based method that happens to be in binary representation and it has many (almost all) the drawbacks of the pre-2008 forms it just operates faster and it does not have as many limitations as a varchar column. All in all I think you put together an interesting method. Great work!Wed, 28 Jan 2009 08:16:04 GMTnoeldhttp://www.sqlservercentral.com/Forums/Topic644681-1312-1.aspxI just feel, to tie myself to a DB structure type, like you mentioned using the hierarchcical ID, might limit my application's portability - say, if I want to take it from MS SQL server to MySQL or Oracle or DB2.Doing a small amount of coding in order to remain with ISO standard fields, is in my opinion, making for a far better application.My 2cents.Wed, 28 Jan 2009 08:15:45 GMTdanielk1