How to write this recursive CTE for SQL Server?

Question

For simplicity my schema:

Folders table (FolderId, ParentFolderId)
DeviceFolderProperties table (FolderId, LogDataRetentionDaysEvent)

Not every folder has a retention day. However this is an inherited value. How can you write something in SQL to return every folder and its retention day and if that is null its inherited value.

There are multiple levels to inheritance, so it will need to walk the tree.

This is what I have tried:

;
WITH [cte]
AS 
(
    SELECT f.FolderId, f.ParentFolderId, dfp.LogDataRetentionDaysEvent
    FROM [Folders] f
    LEFT JOIN DeviceFolderProperties dfp
    ON f.FolderId = dfp.FolderId
), 
[cte_collapse] 
AS --recurse where r days is null
(

    SELECT c.FolderId, c.ParentFolderId, c.LogDataRetentionDaysEvent
    FROM [cte] c
    WHERE c.LogDataRetentionDaysEvent IS NULL

    UNION ALL

    SELECT c.FolderId, c.ParentFolderId, ISNULL(c.LogDataRetentionDaysEvent, cc.LogDataRetentionDaysEvent)
    FROM [cte] c
    JOIN [cte_collapse] cc ON cc.FolderId = c.ParentFolderId

)
SELECT
    *
FROM 
    [cte_collapse]

There's no inheritance in SQL. You're performing a *hierarchical query* using a recursive CTE. A simpler and definitely many times faster way is to use the [hierarchyid type](https://learn.microsoft.com/en-us/sql/t-sql/data-types/hierarchyid-data-type-method-reference?view=sql-server-ver15) instead of parent/child joins. A `hierarchyid` stores the "path" of a hierarchy which makes finding children as easy as looking for rows whose `path` starts with the root path. This search can take advantage of indexes to accelerate the query — Panagiotis Kanavos, Aug 31 '20 at 14:54
[How to create a MySQL hierarchical recursive query](https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query) — philipxy, Aug 31 '20 at 17:34

GMB · Accepted Answer · 2020-08-31T15:04:30.823

You could write this as:

with 
    data as (
        select f.FolderID, f.ParentFolderId, dfp.LogDataRetentionDaysEvent
        from Folders f
        left join DeviceFolderProperties dfp on dfp.FolderID = f.FolderID
    ),
    cte as (
        select d.*, FolderID OriginalFolderId
        from data d
        union all
        select d.*, c.OriginalFolderId
        from cte c
        inner join data d on d.FolderID = c.ParentFolderId
        where c.LogDataRetentionDaysEvent is null
    )
select OriginalFolderId, max(LogDataRetentionDaysEvent) LogDataRetentionDaysEvent
from cte 
group by OriginalFolderId

We start by generating a derived table that contains information from both tables. Then, for each record, the recursive query climbs up the hierarchy, searching for a non-null the retention at each level. The trick is to stop as soon as a match is met.

How to write this recursive CTE for SQL Server?

1 Answers1