25

OK, enough brain cells are dead. SQL Fiddle WITH cte AS ( SELECT [ICFilterID], [ParentID], [FilterDesc], [Active], CAST(0 AS varbinary(max)) AS Level FROM [dbo].[ICFilters] WHERE [ParentID] = 0 UNION ALL SELECT i.[ICFilterID], i.[ParentID], i.[FilterDesc], i.[Active], Level + CAST(i.[ICFilterID] AS ...


11

Using this model, I have come up with a way to query the Pages table in the following manner: SELECT p.* FROM dbo.Pages AS p CROSS APPLY dbo.GetPermissionStatus(p.Id, @CurrentUserId, @PermissionName) AS ps WHERE ps.IsAllowed = 1 ; The GetPermissionStatus inline table-valued function's result can be either an empty set or one single-column row. When ...


10

As your data looks a bit like hierarchyId I thought about using that. Initial versions did not scale well up to 1 million rows, however a few indexing choices on the main temp table have helped. However issues could also be due to my test data, so can you tell me a bit more about your hierarchy? For example how many top-level parents are there, how many ...


9

CONNECT BY is the correct way to handle data that is naturally recursive. I don't know what your table looks like but maybe something like: SELECT * FROM some_table st START WITH st.location = 'BLDG-01' CONNECT BY PRIOR st.location = st.parent; This should get nodes under "BLDG-01". The START WITH clause is your base case. Another explanation (aside ...


9

(O.P.'s note: preferred solution is the 4th / final code block) XML seems to me to be the obvious choice of data structure to use here. with N as ( select T.N from (values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11), (12),(13),(14),(15),(16),(17),(18),(19),(20)) as T(N) ) select top(5 + abs(checksum(newid())) % 15) N1.N as '@Value', ...


8

The GetFamilyTree function in Rolando's answer does not work when the given id is more than 4 integer, because the FORMAT MySQL function adds commas for thousand separators. I have modified the stored function GetFamilyTree to work with big integer ids as below: WHILE queue_length > 0 DO IF queue_length = 1 THEN SET front_id = queue; SET ...


8

I've implemented HierarchyID and found it to provide good performance and easy to use. I've used it on relatively small datasets (tens of thousands of rows) with hierarchy up to 10 branches deep. Why use it? The HierarchyID type provides a number of helper methods (such as IsDescendantOf) that make your job easier than rolling your own materialized path. ...


7

I have seen 2 main ways of enforcing this: 1, the OLD way: CREATE TABLE Foo (FooId BIGINT PRIMARY KEY, ParentFooId BIGINT, FooHierarchy VARCHAR(256), FOREIGN KEY([ParentFooId]) REFERENCES Foo ([FooId]) ) The FooHierarchy column would contain a value like this: "|1|27|425" Where the numbers map to the FooId column. You would then ...


7

Finally I found the solution but it needed some optimization and tweaks to work with my case and I added sorting with ID to get the tree sorted too, the answer is mainly got from here so credit goes to @deceze, CREATE DEFINER=`root`@`localhost` PROCEDURE `tree_recover`() MODIFIES SQL DATA BEGIN DECLARE currentId, currentParentId CHAR(36); ...


7

Add a "path" field and sort by that similar to a file path. As ypercube mentioned, the sorting is overly simplistic in this example and just happens to work but for simplicity's sake I'll leave as is. Most of the time when I use this pattern I sort by name rather than ID anyway. IF OBJECT_ID('[dbo].[btree]', 'U') IS NOT NULL DROP TABLE [dbo].[btree]; ...


7

The technique you are describing for representing task hierarchy is called 'Adjacency list'. Although it is the most intuitive to humans, it doesn't lend itself to very efficient querying in SQL. Other techniques include path enumeration (aka materialized paths) and nested sets. To learn about some other techniques, read this post or search the web for ...


6

It is kind of possible: you can invoke a scalar UDF from you CHECK constraint, and it kind of can detect cycles of any length. Unfortunately, this approach is extremely slow and unreliable: you can have false positives and false negatives. Instead, I would use materialized path. Another way to avoid cycles is to have a CHECK(ID > ParentID), which is ...


6

You are using the Adjacency List model, where it is difficult to enforce such a constraint. You can examine the Nested Set model, where only true hierarchies can be represented (no circular paths). This has other drawbacks though, like slow Inserts/Updates.


6

This is easily achievable with an in-line subquery : select m.id, (select count(*) from messages where parent_id= m.id ) as ChildCount from messages m where m.parent_id = 0 Note that no group by is needed because a sub-query is used.


6

A CROSS APPLY would seem perfect for the job: SELECT v.Nodeid, v.ParentNodeid, v.Header FROM dbo.atable CROSS APPLY ( VALUES (D1id, NULL, 'D1'), (D2id, D1id, 'D2'), (D3id, D2id, 'D3') ) AS v (Nodeid, ParentNodeid, Header) ; For every row of the source dataset, CROSS APPLY produces three, using the VALUES row constructor, ...


6

That was interesting. My aim was to generate given number of levels with random number of child rows per each level in a properly linked hierarchical structure. Once this structure is ready it is easy to add extra info into it like file and folder names. So, I wanted to generate a classic table for storing a tree: ID int NOT NULL ParentID int NULL Lvl int ...


6

A lot of the .NET source code is available, and much of it is Open Source, though under various licenses. Still other code is available, but only for reference purposes and cannot be used in your code. Given that the stated goal is: I would like to increase that limit for a complex existing application that is bound to hit this limit. I don't wish to ...


6

As srutzky pointed you to some locations in his answer where some of Microsoft's source code is available and the function you requested isn't listed, I think we can conclude the source code isn't available under any open source license, and it looks like your question was about it being open source, not about being able to see the source code. Given that ...


6

A common way to do this in SQL Server is to use a Recursive CTE: Here's your test data as an insert: CREATE TABLE #yourmom ( custid INT, custname VARCHAR(10), deptid VARCHAR(3), company VARCHAR(10), parentcustid INT, enrolled BIT ) INSERT #yourmom ( custid, custname, deptid, company, parentcustid, enrolled ) SELECT x.custid, x....


5

The function to use is GetReparentedValue but when using only GetReparentedValue the tree may end up in an "inconsistent" state. Here is some code provided by Microsoft that takes care of that. Moving subtrees. I guess that related to this is Enforcing a tree. It uses a calculated column for parent id that does a self join to the PK.


5

This is an answer to Kirk's question 'why not use it (HierarchyId)'. As compared to materialized path, in some important cases HierarchyId seems to be both less performant and less convenient to work with. The reason is simple: quoting from Microsoft comment on Connect, "The problem is that CLR calls, including hierarchyID's methods, are opaque to the ...


5

If you add another column for the ancestor, e.g. CREATE TABLE [dbo].[tbl_server_group] ( [refid] [int] IDENTITY(1,1) NOT NULL, [refhid] [hierarchyid] NOT NULL, [reflvl] AS refhid.GetLevel() PERSISTED, [refdate] [datetime] NOT NULL DEFAULT GETDATE(), [refname] [varchar](50) NOT NULL, [reflvl1id] AS refhid.GetAncestor(1) ...


5

You could formalize the approach that you're already using and make it more efficient to both maintain and query. Consider an ERD like so: Note that all of your hierarchy is flattened into a single set of columns. There is an involuted relationship so that each child entity in the hierarchy can point to it's immediate parent. I've also included left and ...


5

You don't give a lot of detail on your current structure, so I'm assuming each node in the hierarchy is in a simple table with a single parent relationship like so and the path being a string of ID like /<rootNodeID>/<GPNodeID>/<PNodeID>/<LeadNodeID> and that you intend to store the settings in a property bag arrangement rather than ...


5

Add a column to dbo.Almoxarifado2 that will hold the original value of idAlmoxarifado. Add all rows from dbo.Almoxarifado and then use idAlmoxarifadoOrg to update idAlmoxPai with the value of idMaster for the parent row. Code with some comments: -- Add a column to store original idAlmoxarifado alter table dbo.Almoxarifado2 add idAlmoxarifadoOrg ...


5

I'd try something like this: First, create a new table and copy the existing data there. Then iterate, and in each iteration, remove the last node of each filepath (e.g.: 'A\B\C\D\E' becoming 'A\B\C\D') and add these new filepaths in the table, if they aren't already there. Stop when an iteration doesn't produce new rows. The initialization: -- step 0 ...


5

After reading the documentation, I came to the following conclusions. One difference between the two queries is that the first query has two condition in the connect by , in order to identify a relationship - a row must fulfill not only the parent_id = prior concept_id condition but also the exists clause. connect by parent_id = prior concept_id and ...


4

FrusteratedWithFormsDesigner has the right direction(+1). Here is what I think you are looking for specifically. CREATE OR REPLACE VIEW BuildingSubs AS SELECT connect_by_root location "Building", location "SubLocation" FROM some_table l START WITH l.Location IN ( SELECT location FROM ( SELECT level MyLevel, ...


4

I believe it's possible : create function test_foo (@id bigint) returns bit as begin declare @retval bit; with t1 as (select @id as FooId, 0 as lvl union all select f.FooId , t1.lvl+1 from t1 inner join Foo f ON (f.ParentFooId = t1.FooId) where lvl<11) -- you said that max nested level 10, so if there is any circular -- dependency, we don't ...


4

For immediate parent and its ancestor WITH Parent (refid, Sno, Level) AS ( SELECT refid, Sno, 0 AS Level FROM dbo.MyTable m WHERE Sno=4 UNION ALL SELECT m.refid, m.Sno, Level+1 FROM dbo.MyTable m INNER JOIN Parent p ON p.refid = m.Sno ) SELECT refid, Level FROM Parent; For immediate child and all the grandchild WITH Child (refid, Sno, ...


Only top voted, non community-wiki answers of a minimum length are eligible