InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 20 Jun 2013 05:54:32 GMT20http://www.sqlservercentral.com/Forums/Topic1383887-203-1.aspxJeff,Not putting that back in the original table (mines not employee), I'm just maintaining the mapping in your hierarchy table. My thought is to make your stored procedure more generic, so I can simply pass in the table name, Id/ParentId and the sort field(s) and use dynamic SQL to create a the sorted mapping table. That would then update the rgt/lft extends (your bowers) back into the original table. That way the less knowledgeable developers could implement without having to modify the stored procedure. At least that's my thought, haven't implemented it yet. Most of our hierarchies are in the under 100k row range, most under 10k. We do a lot of hierarchies, product, geo, object, person, etc. This update has provided some great brain food.:-DThanks again,MatthewThu, 14 Mar 2013 18:59:49 GMTLordHzhttp://www.sqlservercentral.com/Forums/Topic1383887-203-1.aspxThanks for the feedback. Basically, great minds think alike because all I did was make a "mapping" table. You could certainly incorporate that into your original employee table but I didn't want to lock the whole table with the update/table scan that would occur. I didn't know how big your table would be.Thu, 14 Mar 2013 17:19:57 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1383887-203-1.aspxJeff,I implemented your sort solution and it is perfect. Only change I need to make is to update the hierarchy table to support the sortedId/sortedParentId and the original ID/parentID fields.Thanks for you assistance and knowledge,Matthew E.Thu, 14 Mar 2013 11:48:29 GMTLordHzhttp://www.sqlservercentral.com/Forums/Topic1383887-203-1.aspxJeff, I had considered that and was working on something along those lines before I left work. For pure performance reasons, I would create a new sorted node mapping table that would maintain the sorted Id/ParentId pairs and a reference to the original Id/ParentId's. Hadn't gotten past that, so if you have a complete solution I would be grateful. Using your method for updating my nested sets has reduced my rebuild time from 18 seconds to 400 milliseconds (8000 nodes, ~10 levels of depth). The only thing missing is solving this sort issue. It would be simpler if the application I was building this for pulled only one level at a time, since I could sort the nodes however I like. But the spec is for a flat table that shows the entire hierarchy and is sorted on a different field.Thanks again,MatthewWed, 13 Mar 2013 22:32:25 GMTLordHzhttp://www.sqlservercentral.com/Forums/Topic1383887-203-1.aspxHere's one way. I apologize for not taking the time to make a more optimal solution but it's been a long day and I'm too pooped to pop.Basically, you need to add an interim table to replace the original Employee table as the source of the recursive CTE to make the sort path. This new table has a SortedChild column to replace the EmployeeID in the sort path calculation and a Parent column to use in the join criteria along with SortedChild.[code="sql"] SELECT SortedChild = ROW_NUMBER() OVER (ORDER BY EmployeeName), Parent = NULL, EmployeeID, ManagerID, EmployeeName INTO #SortedEmployee FROM dbo.Employee;GO CREATE UNIQUE CLUSTERED INDEX IX_SortedEmployee_Composite01 ON #SortedEmployee (EmployeeID); UPDATE child SET child.Parent = parent.SortedChild FROM #SortedEmployee child JOIN #SortedEmployee parent ON parent.EmployeeID = child.ManagerID;--===== Display what we end up with. -- This is not a part of the solution SELECT * FROM #SortedEmployee ORDER BY SortedChild;[/code]Wed, 13 Mar 2013 22:31:22 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1383887-203-1.aspx[quote][b]LordHz (3/13/2013)[/b][hr]Jeff, I have been playing with your RebuildNestedSets_MM stored procedure and I have a question. Is it possible to sort the nodes so that the Left and Right bowers are sorted on another field in the employee table? For example on Employee Name. My issue is I have a large hierarchy and I need the nodes in a specific order and looking through your proc it appears to always build/sort on employeeId. So in your article Lynne comes before Bob because her employeeId is 2 and Bob's is 3. However, I would like to change that sort on name so Bob would appear before Lynne. It looks like part of the beauty of your method is the nodes are arranged by employeeId.If I am missing something obvious, please don't beat me up to much.-Matthew Erdmann[/quote]Heh... that's the problem with articles like this. They're not as big as a book. :-DSomeone smarter than me will probably figure out a better way but, yes, you can sort from left to right by level but not after you've built the nested sets. You have to do it before you build the nested sets and you can't use the EmployeeID for the sort path anymore. Instead, you have to sort by alphabetical position and assign new child/parent values to maintain each nodes position in the alphabetic hierarchy while still maintaining the parent/child hierarchy. That sounds real tough to do but it's actually not. You just need to assign a new sequential value to each employee in alphabetical order and then find the match of that new number as the new value for each manager. It does require a couple of extra steps (sorting is always expensive) but the code runs so fast, I don't see what an extra minute or two on a million rows would be much of an inconvenience. And, no... I wouldn't use a self joining CTE for this because that'll get calculated more than once. You need a table to store it in, anyway.I'll post the code in a couple of minutes.Wed, 13 Mar 2013 21:55:13 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1383887-203-1.aspxJeff, I have been playing with your RebuildNestedSets_MM stored procedure and I have a question. Is it possible to sort the nodes so that the Left and Right bowers are sorted on another field in the employee table? For example on Employee Name. My issue is I have a large hierarchy and I need the nodes in a specific order and looking through your proc it appears to always build/sort on employeeId. So in your article Lynne comes before Bob because her employeeId is 2 and Bob's is 3. However, I would like to change that sort on name so Bob would appear before Lynne. It looks like part of the beauty of your method is the nodes are arranged by employeeId.If I am missing something obvious, please don't beat me up to much.-Matthew ErdmannWed, 13 Mar 2013 09:07:13 GMTLordHzhttp://www.sqlservercentral.com/Forums/Topic1383887-203-1.aspx[quote][b]Lynn Pettis (11/29/2012)[/b][hr][quote][b]Jeff Moden (11/29/2012)[/b][hr][quote][b]Lynn Pettis (11/28/2012)[/b][hr]Combined some of the code from your code in this article with your delimited split routine to solve a problem at work. Inspiration comes from many places.Have to give it to you Jeff, you are the Mentor of the Year in my book.[/quote]:blush: Thanks, Lynn. I really appreciate that. What did you end up doing?[/quote]Needed to sort DD Version Keys (x.x.x, like 2.1.10). The original code had no problems as it didn't have to work with a 2 digit value. When we went to 2.1.10 instead of 2.3.1, it was a problem. The original solution by another DBA used the OLE Automation Procedures to create a regex_replace function. Only problem is that there is a STIG requiring that these procedures be disabled.I'm sure there may be another solution, but combining the DelimitedSplit8K and the varbinary concatenation from this article I was able to generate a solution that correctly sorted the version keys, including a very unlikely set of numerical elements (x(n).x(n).x(n).x(n).x(n).x(n).x(n).x(n).x(n).x(n).x(n)) where n represents 1 or more digits.I wouldn't have thought of concatenating varbinary values had I not read this article.[/quote]VERY cool. Thanks for posting the usage, Lynn.Fri, 30 Nov 2012 10:22:38 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1383887-203-1.aspxLynn,I see you too have run into this 'x.x.x...' problem.I have run into serveral end user applications where this was used to manage hierarchies. Of course, all 'order by' clauses became useless when one of the x values reached 10.I solved it by writing a function which converts 'x.x.x...' to two digit values starting at a certain level.Fri, 30 Nov 2012 01:28:24 GMTMichael Meierruthhttp://www.sqlservercentral.com/Forums/Topic1383887-203-1.aspx[quote][b]Jeff Moden (11/29/2012)[/b][hr][quote][b]Lynn Pettis (11/28/2012)[/b][hr]Combined some of the code from your code in this article with your delimited split routine to solve a problem at work. Inspiration comes from many places.Have to give it to you Jeff, you are the Mentor of the Year in my book.[/quote]:blush: Thanks, Lynn. I really appreciate that. What did you end up doing?[/quote]Needed to sort DD Version Keys (x.x.x, like 2.1.10). The original code had no problems as it didn't have to work with a 2 digit value. When we went to 2.1.10 instead of 2.3.1, it was a problem. The original solution by another DBA used the OLE Automation Procedures to create a regex_replace function. Only problem is that there is a STIG requiring that these procedures be disabled.I'm sure there may be another solution, but combining the DelimitedSplit8K and the varbinary concatenation from this article I was able to generate a solution that correctly sorted the version keys, including a very unlikely set of numerical elements (x(n).x(n).x(n).x(n).x(n).x(n).x(n).x(n).x(n).x(n).x(n)) where n represents 1 or more digits.I wouldn't have thought of concatenating varbinary values had I not read this article.Thu, 29 Nov 2012 21:23:32 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1383887-203-1.aspx[quote][b]Lynn Pettis (11/28/2012)[/b][hr]Combined some of the code from your code in this article with your delimited split routine to solve a problem at work. Inspiration comes from many places.Have to give it to you Jeff, you are the Mentor of the Year in my book.[/quote]:blush: Thanks, Lynn. I really appreciate that. What did you end up doing?Thu, 29 Nov 2012 20:51:28 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1383887-203-1.aspxCombined some of the code from your code in this article with your delimited split routine to solve a problem at work. Inspiration comes from many places.Have to give it to you Jeff, you are the Mentor of the Year in my book.Wed, 28 Nov 2012 18:50:46 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1383887-203-1.aspx[quote][b]Theo Ekelmans (11/13/2012)[/b][hr]LOL, gimme thor class hammer!!!The project i'm working on is a roof of concept SNMP MIB database, that is so fast it can do (near)realtime SNMP data packet to SQL data conversionJust in case your new to the extensible SNMP MIB tree:If E-mail addresses were OIDs... user@ordina.org would have been something like: user@ordina.enterprises.private.internet.dod.org.isoor in non-translated notation: user@99999.1.4.1.6.3.1except that we write the top-most part at the left: 1.3.6.1.4.1.99999.117.115.101.114 An OID is just a unique key (within one managed device) for one piece of information Ensures vendors don't have conflicting OIDs OID corresponds to a label .1.3.6.1.2.1.1.5 => sysName (in windows this is the servername)The complete (translated) path: .iso.org.dod.internet.mgmt.mib-2.system.sysName The info needed to get from one to an other is "mib (database) files", that are supplied with each new device / software version.The provided MIB files all have a part of the ginormous jigsaw MIB tree, and that needs to be searched between 500 to 20.000 times a minute.Fun project indeed :)[/quote]I tried this once, so I feel your pain.The best I got was to store the OIDs flat in an indexed VARCHAR(1000) column and my metadata sparsely around that. Given that OIDs are essentially a materialized-path hierarchy, anyway, I was able to do parent>child navigation by `WHERE oid LIKE @parent_oid + '%'` and child>parent by `WHERE oid = LEFT(@child_oid, LEN(@child_oid) - CHARINDEX('.', REVERSE(@child_oid)))` (plus/minus a 1, here or there!).Maybe you could get some benefit from the binary approaches Jeff is using instead of the string-based approach I once took. I can't remember if there is an upper limit to the size of each 'part' of an OID but, if I were to guess, it would be a 0-64K 'word', which would convert to SMALLINT and on to BINARY(2) well enough (if it is actually a 0-4G dword, you would need to convert to INT and on to BINARY(4)).As part of your MIB-loading routine, split the OIDs by '.' [Jeff's earlier article on string-splitting (and the great discussion around it) at http://www.sqlservercentral.com/articles/Tally+Table/72993/ might be of help here], then convert each word to SMALLINT, then BINARY(2) and store the materialized path as concatenations of BINARY(2) into a indexed VARBINARY(2000).With a materialized path of fixed-size binary elements instead of variable-length string elements, you could get 'level' metadata from `DATALENGTH(oid) / 2`, child>parent from `SUBSTRING(@oid, 1, DATALENGTH(@oid) - 2)` and parent>child from `WHERE SUBSTRING(oid, 1, DATALENGTH(@parent_oid)) = @parent_oid`.If binary sorts are that good (and people here give me every reason to think that they are), you may be performant enough at this point.Thu, 22 Nov 2012 12:26:53 GMTjimbobmcgeehttp://www.sqlservercentral.com/Forums/Topic1383887-203-1.aspx[QUOTE] To wit, even you write non-portable code. For example, the very first piece of code you offer in your fine book titled "Trees and Hierarchies in SQL for Smarties" (1st edition) has the following code on page 6.[/QUOTE] That code is Pascal and I think it still ports. I dropped in it the second edition in favor of SQL/PSM code.Sat, 17 Nov 2012 19:23:26 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic1383887-203-1.aspx[quote][b]Thomas S. Trias (11/15/2012)[/b][hr]Excellent article; I have used all three types of tree representations before. In one project, we used a materialized path, and I kind of wish I'd thought to use varbinary instead of a hex string. However, I just tested using an index on a materialized path as a hex string using 100000 rows of sample data generated with BuildLargeEmployeeTable, and the results were perhaps not so surprising.[code="sql"]SELECT ISNULL(EmployeeID, 0) AS EmployeeID, ISNULL(ManagerID, 0) AS ManagerID, ISNULL(CONVERT(varchar(8000), sortpath, 2), '') as SortPathINTO Hierarchy2FROM HierarchyALTER TABLE Hierarchy2 ADD CONSTRAINT PK_Hierarchy2 PRIMARY KEY CLUSTERED (EmployeeID)CREATE INDEX SortPath ON Hierarchy2 (SortPath)[/code]SQL Server will use a string index on LIKE operations anchored at the begining, so this:[code="sql"]SELECT * FROM Hierarchy2 WHERE SortPath LIKE '0000000100000002000000130000001F0000003F00000058000000650000009600000935%'[/code]ends up being more than three times more efficient than this:[code="sql"]SELECT * FROM Hierarchy2 WHERE SUBSTRING(SortPath, 65, 8) = '00000935'[/code]Unfortunately, the index only supports the first 900 bytes of the SortPath. Also, since the Nested Sets provide the same benefits (fast ancestor / descendant access and sorting), and since the update to calculate them using the SortPath is going to touch every row anyway, going with the approach you've got looks best. :-)I guess since the rebuild is so fast, you also don't need to consider using double-precision floating-point numbers as the bowers (which makes updating the bowers in place a bit more manageable, since you can keep subdividing the ranges up to a certain point).Keep up the great work![/quote]Thanks for the feedback, Thomas. On the index thing, it turns out that adding even the final indexes before the data is complete will actually slow the code down. As you point out, since we're updating the whole table with each pass, indexes serve no real benefit to performance until all is said and done.Thu, 15 Nov 2012 23:22:59 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1383887-203-1.aspx[quote][b]GSquared (11/15/2012)[/b][hr]Since I was out a few days, I missed this article till today.Interesting methodology.Testing it on my workstation, I got 45 seconds for a million-row hierarchy, 30 nodes deep.On 10k rows, 21 deep, took 593 milliseconds.Methods I posted in my article in August took 3 milliseconds at 10k rows, 29 seconds at 1M rows, same data. I'm using HierarchyIDs the same way you use your SortBy column. When I modified to Varbinary(max) instead of HiearchyID, it stays under 10 milliseconds for 10k rows, but goes up to 49 seconds for 1M rows, using those methods.In the Varbinary(max) version, 70-75% of the run-time was generating the breadcrumb path (SortBy), and 25-30% in generating the Nested Sets from it.Per my tests, HierarchyID will only easily go to a depth of a little more than 400 levels, so my methods won't work precisely on really deep hierarchies. Anything less than 400 levels, I'd still stick with HierarchyID, and seriously think about upgrading to a supported version of SQL Server if you can't use them. Not always possible, but it is worth it if you can.Didn't get a chance to try yours out with a hierarchy table with n-top levels, instead of just 1, as per the comments in your code. Wanted to make sure I was using the same testing methodology, so didn't play with your test-hierarchy-generator, in order to try these kinds of permutations.Of course, I'm not entirely sure why you'd use both breadcrumbs and nested sets. If you're already breadcrumbing, you can query that directly and avoid nested sets completely. Same performance as nested sets, in most cases, unless your path is really, really long (since it's a binary sort, until it goes over 8000 datalength; indexes do binary sorts very, very well).[/quote]Thanks for the feedback, Gus. The bread crumbing was just to make the calculation of the Nested Sets easy and could actually be removed from the final table. I left it there for two reasons... 1) as a training artifact and 2) beccause just like you say, there's a use in those breadcrumbs that will be demmonstrated in part 2.So far as upgrading to 2008 or better to make use of the HierarchyID datatype goes, some people just don't have that option.Thu, 15 Nov 2012 23:18:32 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1383887-203-1.aspxExcellent article; I have used all three types of tree representations before. In one project, we used a materialized path, and I kind of wish I'd thought to use varbinary instead of a hex string. However, I just tested using an index on a materialized path as a hex string using 100000 rows of sample data generated with BuildLargeEmployeeTable, and the results were perhaps not so surprising.[code="sql"]SELECT ISNULL(EmployeeID, 0) AS EmployeeID, ISNULL(ManagerID, 0) AS ManagerID, ISNULL(CONVERT(varchar(8000), sortpath, 2), '') as SortPathINTO Hierarchy2FROM HierarchyALTER TABLE Hierarchy2 ADD CONSTRAINT PK_Hierarchy2 PRIMARY KEY CLUSTERED (EmployeeID)CREATE INDEX SortPath ON Hierarchy2 (SortPath)[/code]SQL Server will use a string index on LIKE operations anchored at the begining, so this:[code="sql"]SELECT * FROM Hierarchy2 WHERE SortPath LIKE '0000000100000002000000130000001F0000003F00000058000000650000009600000935%'[/code]ends up being more than three times more efficient than this:[code="sql"]SELECT * FROM Hierarchy2 WHERE SUBSTRING(SortPath, 65, 8) = '00000935'[/code]Unfortunately, the index only supports the first 900 bytes of the SortPath. Also, since the Nested Sets provide the same benefits (fast ancestor / descendant access and sorting), and since the update to calculate them using the SortPath is going to touch every row anyway, going with the approach you've got looks best. :-)I guess since the rebuild is so fast, you also don't need to consider using double-precision floating-point numbers as the bowers (which makes updating the bowers in place a bit more manageable, since you can keep subdividing the ranges up to a certain point).Keep up the great work!Thu, 15 Nov 2012 16:22:33 GMTThomas S. Triashttp://www.sqlservercentral.com/Forums/Topic1383887-203-1.aspxSince I was out a few days, I missed this article till today.Interesting methodology.Testing it on my workstation, I got 45 seconds for a million-row hierarchy, 30 nodes deep.On 10k rows, 21 deep, took 593 milliseconds.Methods I posted in my article in August took 3 milliseconds at 10k rows, 29 seconds at 1M rows, same data. I'm using HierarchyIDs the same way you use your SortBy column. When I modified to Varbinary(max) instead of HiearchyID, it stays under 10 milliseconds for 10k rows, but goes up to 49 seconds for 1M rows, using those methods.In the Varbinary(max) version, 70-75% of the run-time was generating the breadcrumb path (SortBy), and 25-30% in generating the Nested Sets from it.Per my tests, HierarchyID will only easily go to a depth of a little more than 400 levels, so my methods won't work precisely on really deep hierarchies. Anything less than 400 levels, I'd still stick with HierarchyID, and seriously think about upgrading to a supported version of SQL Server if you can't use them. Not always possible, but it is worth it if you can.Didn't get a chance to try yours out with a hierarchy table with n-top levels, instead of just 1, as per the comments in your code. Wanted to make sure I was using the same testing methodology, so didn't play with your test-hierarchy-generator, in order to try these kinds of permutations.Of course, I'm not entirely sure why you'd use both breadcrumbs and nested sets. If you're already breadcrumbing, you can query that directly and avoid nested sets completely. Same performance as nested sets, in most cases, unless your path is really, really long (since it's a binary sort, until it goes over 8000 datalength; indexes do binary sorts very, very well).Thu, 15 Nov 2012 13:59:06 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic1383887-203-1.aspx@mister.magoo[quote]both are hacks [/quote]Yes I totally agree. I was just using the @@spid as an example. My point (that if you're using obscure hacks you should explain what you're doing in your code) applies equally to using NULL in that context.Wed, 14 Nov 2012 17:10:29 GMTGPOhttp://www.sqlservercentral.com/Forums/Topic1383887-203-1.aspx[quote][b]GPO (11/14/2012)[/b][hr][quote]row_number() over (order by @@spid) [/quote]I'm not against using obscure hacks, and there's always going to be a debate about the point at which you even classify something as an obscure hack. The discovery of a new, better way of doing things will start life as an obscure hack and eventually find its way into mainstream use. What I am against, is using obscure hacks without explaining what you're doing in your code. At some point Joe Rookie is going to inherit my code and is going to waste an awful lot of time working out why I'd ever order by @@spid if I haven't commented. I don't know where the line should be drawn regarding what to comment and what not to, but personally, I've always appreciated inheriting code where the original author went to a bit of effort.[/quote]Each to their own, but to my mind, I see no difference between ordering by a static variable like @@SPID and a NULL set...both are hacks according to [url=http://msdn.microsoft.com/en-us/library/ms189461(v=sql.90).aspx]Books Online[/url], which only allows for columns made available by the FROM clause...[quote]<ORDER BY Clause>Specifies the order to apply the ranking window function. For more information, see ORDER BY Clause (Transact-SQL).Important:When used in the context of a ranking window function, <ORDER BY Clause> can only refer to columns made available by the FROM clause. An integer cannot be specified to represent the position of the name or alias of a column in the select list.[/quote]Wed, 14 Nov 2012 16:51:41 GMTmister.magoohttp://www.sqlservercentral.com/Forums/Topic1383887-203-1.aspx[quote]row_number() over (order by @@spid) [/quote]I'm not against using obscure hacks, and there's always going to be a debate about the point at which you even classify something as an obscure hack. The discovery of a new, better way of doing things will start life as an obscure hack and eventually find its way into mainstream use. What I am against, is using obscure hacks without explaining what you're doing in your code. At some point Joe Rookie is going to inherit my code and is going to waste an awful lot of time working out why I'd ever order by @@spid if I haven't commented. I don't know where the line should be drawn regarding what to comment and what not to, but personally, I've always appreciated inheriting code where the original author went to a bit of effort.Wed, 14 Nov 2012 14:50:14 GMTGPOhttp://www.sqlservercentral.com/Forums/Topic1383887-203-1.aspx[quote][b]mister.magoo (11/14/2012)[/b]you can use[code="sql"]row_number() over (order by @@spid) [/code][/quote]Yes, that's the one!Wed, 14 Nov 2012 07:37:36 GMTMichael Meierruthhttp://www.sqlservercentral.com/Forums/Topic1383887-203-1.aspx[quote][b]Michael Meierruth (11/14/2012)[/b][hr]MM and Mark,This is what I was looking for.[code]select top 1000 identity(int,1,1) as Ninto dbo.HTallyfrom master.sys.all_columns ac1 cross join master.sys.all_columns ac2[/code]But you would then have to do an update to set N=(N-1)*4+1.So row_number() on (order by (select @@spid)) is fine with me.[/quote]you can use[code="sql"]row_number() over (order by @@spid) [/code]you don't need [code="sql"]row_number() over (order by (select @@spid))[/code]Wed, 14 Nov 2012 07:13:19 GMTmister.magoohttp://www.sqlservercentral.com/Forums/Topic1383887-203-1.aspxMM and Mark,This is what I was looking for.[code]select top 1000 identity(int,1,1) as Ninto dbo.HTallyfrom master.sys.all_columns ac1 cross join master.sys.all_columns ac2[/code]But you would then have to do an update to set N=(N-1)*4+1.So row_number() on (order by (select @@spid)) is fine with me.Wed, 14 Nov 2012 07:10:46 GMTMichael Meierruthhttp://www.sqlservercentral.com/Forums/Topic1383887-203-1.aspx[quote][b]James A Skipwith (11/14/2012)[/b][hr]Smashing article Jeff. Binary sorts rule! I have been using these with recursive CTEs for a few years now and they rock!!![/quote]Thanks for the great feedback, James!Wed, 14 Nov 2012 06:59:52 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1383887-203-1.aspx[quote][b]Michael Meierruth (11/14/2012)[/b]In fact a table containing a single int column which is also an identity column cannot be filled using 'insert into'.[/quote]You can use INSERT INTO thetable DEFAULT VALUES;Wed, 14 Nov 2012 06:29:46 GMTMark-101232http://www.sqlservercentral.com/Forums/Topic1383887-203-1.aspx[quote][b]Michael Meierruth (11/14/2012)[/b][hr]In fact, in place of '(select 1)' you can use anything that's not a constant, e.g. @@servername, getdate(), etc. (but for some strange reason it doesn't like @@datefirst)[/quote]Yes, my favourite has always been @@spid because it's quick to type.[quote]In fact a table containing a single int column which is also an identity column cannot be filled using 'insert into'.[/quote]Unless you "set identity_insert mytable on" of course.Wed, 14 Nov 2012 06:26:42 GMTmister.magoohttp://www.sqlservercentral.com/Forums/Topic1383887-203-1.aspxSmashing article Jeff. Binary sorts rule! I have been using these with recursive CTEs for a few years now and they rock!!!Wed, 14 Nov 2012 02:27:46 GMTJames A Skipwithhttp://www.sqlservercentral.com/Forums/Topic1383887-203-1.aspx[quote][b]SQLRNNR (11/13/2012)[/b][hr]Nice job Jeff.[/quote]Thanks, Jason. I appreciate it. It looks so simple now but this one took a long time to research and tweak.Wed, 14 Nov 2012 01:58:55 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1383887-203-1.aspx[quote][b]Jeff Moden (11/14/2012)[/b]I believe the question was actually about how to use the table from the article... not the HierarchyID. [/quote]Oops, you're rightWed, 14 Nov 2012 01:56:51 GMTMichael Meierruthhttp://www.sqlservercentral.com/Forums/Topic1383887-203-1.aspx[quote][b]Michael Meierruth (11/14/2012)[/b][hr][quote][b]Cyclone (11/14/2012)[/b][hr]Hi ...This may be a silly question, but how does one use the dbo.Hierarchy table ?Thanks.[/quote]If you know absolutely nothing about HierarchyID this is a good place to start: [url]http://www.sqlservercentral.com/articles/SQL+Server+2008/62204/[/url][/quote]I believe the question was actually about how to use the table from the article... not the HierarchyID. @Cyclone,To answer the question that I think your asking, that's some of the "hundreds of chapters and thousands of articles" thing that I talked about in the article. The final dbo.Hierarchy table ends up having 3 different types of hierarical structures (Adjacency List, Materialized Path, and Nested Sets) in it. Each structure has it's own advantages that are quite different between the 3 structures. The article summarized that the Adjacenncy List is very easy to maintain and that the Nested Sets screams for reporting performance. Since the subjects are so large, I recommend you get a copy of Joe Celko's book that both he and I have already mentioned. Joe Celko has also posted a wad of information about the Nested Sets contain in the dbo.Hierarchy table and you should be able to find that pretty easily by doing a search for "Celko Nested Sets".Wed, 14 Nov 2012 01:46:47 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1383887-203-1.aspx[quote][b]jimbobmcgee (11/13/2012)[/b][hr][quote][b]Michael Meierruth (11/13/2012)[/b][hr]What is this '(select null)' trick all about?[/quote]It's a trick to 'order' by constant value, i.e. to not order/sort at all; in this case it adds a row number number based on the natural order that the processor hit the rows, rather than pre-sorting by a given column(set). You can't just do OVER(ORDER BY 1), because it interprets it as attempting to use the first column in the select, which OVER() won't let you do, but it does seem to let you get away with OVER(ORDER BY (SELECT 1)) [or, in this case, OVER(ORDER BY (SELECT NULL))].I can't see in the BOL syntax tree where a select is possible (bar some vague reference to an 'expression') but it's been stable enough for me in the past. Maybe someone with a keener understanding of the nuances of the BOL can spot the bit that makes it a 'documented feature'.In any case, I have often wondered if it might be equivalent to `OVER (ORDER BY the_clustered_index_col)` (at least for a plain table), or whether that would incur more reads. I have never bothered to try because OVER(ORDER BY (SELECT NULL)) always worked well enough for me.[/quote]I'll keep this in my drawer of tricks. In fact, in place of '(select 1)' you can use anything that's not a constant, e.g. @@servername, getdate(), etc. (but for some strange reason it doesn't like @@datefirst)In fact a table containing a single int column which is also an identity column cannot be filled using 'insert into'.Wed, 14 Nov 2012 01:41:40 GMTMichael Meierruthhttp://www.sqlservercentral.com/Forums/Topic1383887-203-1.aspx[quote][b]jimbobmcgee (11/13/2012)[/b][hr][quote][b]Michael Meierruth (11/13/2012)[/b][hr]What is this '(select null)' trick all about?[/quote]It's a trick to 'order' by constant value, i.e. to not order/sort at all; in this case it adds a row number number based on the natural order that the processor hit the rows, rather than pre-sorting by a given column(set). You can't just do OVER(ORDER BY 1), because it interprets it as attempting to use the first column in the select, which OVER() won't let you do, but it does seem to let you get away with OVER(ORDER BY (SELECT 1)) [or, in this case, OVER(ORDER BY (SELECT NULL))].I can't see in the BOL syntax tree where a select is possible (bar some vague reference to an 'expression') but it's been stable enough for me in the past. Maybe someone with a keener understanding of the nuances of the BOL can spot the bit that makes it a 'documented feature'.In any case, I have often wondered if it might be equivalent to `OVER (ORDER BY the_clustered_index_col)` (at least for a plain table), or whether that would incur more reads. I have never bothered to try because OVER(ORDER BY (SELECT NULL)) always worked well enough for me.[/quote]Thank you for the fine cover on this question. I had a heck of a time at work today because of some problems with some 3rd party code. Didn't get a fix in until just a couple of hours ago.Wed, 14 Nov 2012 01:36:17 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1383887-203-1.aspx[quote][b]Michael Meierruth (11/13/2012)[/b][hr]Jeff,In the code for generating the tally table, you use this syntax:row_number() over (order by (select null))I have never see this before. But it works. What is this '(select null)' trick all about?PSVery, very neat article![/quote]Thanks, Michael. And if I haven't said it recently, thanks again for the help way back on the "reducing spaces" discussion from a couple of years ago.jimbobmcgee nailed the explanation of the "SELECT null". The ORDER BY doesn't like naked constants even if they're not numeric. Let's hope that MS doesn't deprecate that because it'll tick off a whole lot of people that are using it in a whole lot of places.Wed, 14 Nov 2012 01:32:58 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1383887-203-1.aspx[quote][b]IgorMi (11/13/2012)[/b][hr]Hi Jeff,Simply brilliant! ThanksIgorMi[/quote]Thanks, Igor. I really appreciate the compliment. :blush:Wed, 14 Nov 2012 01:26:55 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1383887-203-1.aspx[quote][b]Cyclone (11/14/2012)[/b][hr]Hi ...This may be a silly question, but how does one use the dbo.Hierarchy table ?Thanks.[/quote]If you know absolutely nothing about HierarchyID this is a good place to start: [url]http://www.sqlservercentral.com/articles/SQL+Server+2008/62204/[/url]Wed, 14 Nov 2012 01:26:08 GMTMichael Meierruthhttp://www.sqlservercentral.com/Forums/Topic1383887-203-1.aspx[quote][b]Theo Ekelmans (11/13/2012)[/b][hr]LOL, gimme thor class hammer!!!The project i'm working on is a roof of concept SNMP MIB database, that is so fast it can do (near)realtime SNMP data packet to SQL data conversionJust in case your new to the extensible SNMP MIB tree:If E-mail addresses were OIDs... user@ordina.org would have been something like: user@ordina.enterprises.private.internet.dod.org.isoor in non-translated notation: user@99999.1.4.1.6.3.1except that we write the top-most part at the left: 1.3.6.1.4.1.99999.117.115.101.114 An OID is just a unique key (within one managed device) for one piece of information Ensures vendors don't have conflicting OIDs OID corresponds to a label .1.3.6.1.2.1.1.5 => sysName (in windows this is the servername)The complete (translated) path: .iso.org.dod.internet.mgmt.mib-2.system.sysName The info needed to get from one to an other is "mib (database) files", that are supplied with each new device / software version.The provided MIB files all have a part of the ginormous jigsaw MIB tree, and that needs to be searched between 500 to 20.000 times a minute.Fun project indeed :)[/quote]Sounds [i]VERY [/i]interesting and fun. Thank you very much for the overview on your project. Unfortunately and now that I see what you're doing, I don't believe this coming Thursday's article will help you much. If level based counts and sums are important, it might.Wed, 14 Nov 2012 01:24:21 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1383887-203-1.aspx[quote][b]cs_troyk (11/13/2012)[/b][hr]Excellent article, Jeff! I look forward to Thursday's installment.I've played around with mixing these two representations of hierarchies before, but had never gotten past the performance issues, so it's great to have a new approach to try. It seems that I keep inheriting db implementations that contain at least one hierarchy in them, so having more choices for dealing with them is a very good thing!For those that are willing to give up a little in terms of understandability (which can be dealt with via abstractions, anyway), and who are working with fairly constrained hierarchy sizes, take some time to investigate the [url=http://www.dba-oracle.com/t_sql_patterns_matrix_encoding.htm]matrix path encoding[/url] technique that Tropashko describes. You can think of it as a "best of both worlds" approach to the problem that doesn't have the heavy recalculation requirements. I was also able to obtain favorable performance results for many of the descendant- and ancestor-type queries. If nothing else, it will expand your db math skillset:-DAnd finally, for those with some disk space to burn, you might consider materializing the transitive closure of the hierarchy. This can really net you some performance if you have a large hierarchy that is used primarily for reads (the materialization is a rather expensive process).-TroyK[/quote]Thanks for the great feedback, Troy. I'll take a look at the link you so kindly provided.Shifting gears, I hope Thursday's article will help solve some of the problems for large hierarchies that are read heavy.Wed, 14 Nov 2012 01:21:38 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1383887-203-1.aspx[quote][b]CELKO (11/13/2012)[/b][hr]This was really good![/quote]Thanks, Joe. I appreciate you stopping by especially with the code to convert Nested Sets to an Adjaceny List.[quote] I would drop the needlessly proprietary code (ISNULL? We have COALESCE today, CAST not CONVERT, AS not = for aliases, etc). [/quote]I appreciate your thoughts on that but as soon as you use a variable (or just about anything else of particular good use), you lose portability no matter which database engine you're writing code for. Besides true portability being a myth, I've never believed in trying to make code truly portable because you can miss out on a lot of the horsepower of some of the extensions to SQL. For example, I used the proprietary ISNULL because it's a bit faster than COALESCE for several reasons. I also intentionally used the proprietary UPDATE to avoid doing multiple table scans during multiple updates to do what could be done in a single scan to create the Left and Right Bowers. Most other engines won't allow you to update a variable and a column at the same time and that's a real shame but I'm still going to use the technique in SQL Server because it can be used to easily avoid multiple and, therefor, unnecessary table scans.I also use the [i]alias = expression [/i] form of assignment for several reasons... it's the same form as the UPDATE statement in virtually any SQL engine and it makes the code a whole lot easier to read because the all-important column alias will always start at character 9 in my formatted code. No need to look for it at the ragged-right end of multiple length expressions to find the target.To wit, even you write non-portable code. For example, the very first piece of code you offer in your fine book titled "Trees and Hierarchies in SQL for Smarties" (1st edition) has the following code on page 6.[code="sql"]DECLARE [font="Arial Black"]ARRAY [/font]GraphList[font="Arial Black"]OF RECORD [/font][edge CHAR(1), in_node INTEGER, out_node INTEGER];[/code]ANSI or not, that's not truly portable code because it doesn't port to SQL Server. ANSI means nothing when it comes to portability except maybe for simple CRUD procs (which are normally taken care of by various ORMs now-a-days) because no database engine is 100% ANSI compliant even on such simple things as how to declare a variable. I rue the day that they ever are because there would be no competition to improve. ;-) In fact, ANSI has adopted many things as “ANSI Standard” that were once proprietary extensions to various dialects of SQL.Heh… and let’s see you port a trigger between Oracle and SQL Server with no changes. Now THAT’s some serious “fun”!The same is true on the Oracle side of the fence (for example). If someone were to try something similar to this article in Oracle, I'd tell them not to bother because it's nearly as (or maybe even more so) efficient to use CONNECT BY for such things. If it turned out that I could get even more speed using something proprietary to Oracle, I'd use that instead because we're not playing around with 10 or even 10 thousand rows. When you work with millions of rows, every microsecond you can save per "function" per row adds up in a hurry.Like I said, I appreciate your thoughts on the subject but will continue to use and advertise the power of the SQL extensions for whatever database engine I happen to be working in to get the best performance possible. Besides, I've only had to port code once since 1995 and it just isn't that difficult to do. It's just not worth crippling the performance of code by avoiding the proprietary extensions just on the very remote chance that you might have to port code someday.BWAAA-HAAAA!!! Imagine it if someone told you you couldn't use the code from the previously cited page 6 because it wouldn't port to SQL Server. That would be a fun moment to watch! :w00t:Got features? Use them! ;-)Wed, 14 Nov 2012 01:15:11 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1383887-203-1.aspxHi ...This may be a silly question, but how does one use the dbo.Hierarchy table ?Thanks.Wed, 14 Nov 2012 01:01:30 GMTCyclone