tag:blogger.com,1999:blog-7079873813109086690.post9167931010379907760..comments2023-06-20T21:38:12.042-07:00Comments on ...juggling with .net & sql: SQL Server # Storing Hierarchical Data – Parent Child n’th level # TSQLvinay pugaliahttp://www.blogger.com/profile/07027426501611091260noreply@blogger.comBlogger4125tag:blogger.com,1999:blog-7079873813109086690.post-6094819629055230542012-03-28T21:21:57.778-07:002012-03-28T21:21:57.778-07:00Thanks for reverting so promptly. This is what I w...Thanks for reverting so promptly. This is what I want to know - how the level is contrived?<br /><br />Can you please elaborate more on this. I will try and correct myself if at all what you are saying is correct.vinay pugaliahttps://www.blogger.com/profile/07027426501611091260noreply@blogger.comtag:blogger.com,1999:blog-7079873813109086690.post-65938988476095925732012-03-28T21:13:11.706-07:002012-03-28T21:13:11.706-07:00Hello,
Thanks for posting back your concerns. I wi...Hello,<br />Thanks for posting back your concerns. I will try and answer them taking one at a time - <br /><br />1. "recursive cte does a better job of building a hierarchical structure" - Could you please explain what exactly do you mean by "better job"; I mean in which context? If you are talking about performance, then I would request you to please come up with your test script to support it.<br /><br />2. "no relationship to the new hierarchyid" - Yes, you are correct as the intention was to come up with a simple logic which could be used for the versions lower than 2008.<br /><br />3. "The level logic is contrived and only works correctly on single digit numbers" - Below is the script which proves it even works for multiple digits<br /><br />;WITH CTE AS (<br />SELECT 1 EmployeeID,NULL ManagerID, '\1\' [Path]<br />UNION ALL <br />SELECT 12 EmployeeID,1 ManagerID, '\1\12\' [Path]<br />UNION ALL <br />SELECT 3 EmployeeID,1 ManagerID, '\1\3\' [Path]<br />UNION ALL <br />SELECT 24 EmployeeID,2 ManagerID, '\1\12\24\' [Path]<br />UNION ALL <br />SELECT 5 EmployeeID,4 ManagerID, '\1\12\24\5\' [Path]<br />)<br />SELECT<br /> *,<br /> (LEN([Path]) - LEN(REPLACE([Path],'\',''))) - 2 [Level]<br />FROM<br /> CTE<br />WHERE<br /> [Path] LIKE '%\12\%'<br /><br />Please revert if you feel otherwise.vinay pugaliahttps://www.blogger.com/profile/07027426501611091260noreply@blogger.comtag:blogger.com,1999:blog-7079873813109086690.post-52224533686688500962012-03-28T21:01:16.232-07:002012-03-28T21:01:16.232-07:00Sorry, I was wrong about the 2 digit numbers, but ...Sorry, I was wrong about the 2 digit numbers, but the level is still contrived.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-7079873813109086690.post-45568987182382494612012-03-28T20:31:52.591-07:002012-03-28T20:31:52.591-07:00Pretty bad example. Even SQL help's recursive ...Pretty bad example. Even SQL help's recursive cte does a better job of building a hierarchical structure. Has no relationship to the new hierarchyid. The level logic is contrived and only works correctly on single digit numbers. (See GetLevel method of hierarchyid)Anonymousnoreply@blogger.com