Recursive Function in MS SQL

Question

I have following two tables:

SELECT [AM_ID]
  ,[AMI_ID]
  ,[Parent_AMI_ID]
  ,[AMI_Code]
FROM [Reporting].[dbo].[DIM_AM]
;

This table contains hierarchy (37 rows)

SELECT [AMI_ID]
  ,[AMI_Name]
FROM [Reporting].[dbo].[DIM_AMI]
;

This table contains entities (AMI_Name).

And i have to make a table using recursive function in MS SQL that will return following:

SELECT
Level
,AMI_Code
,AMI_Name
FROM...

How can I use the recursive function to generate hierarhy in this format? How do you write recursive query with recursive function in MS SQL in General? Give me some Suggestion please.

Possible duplicate of [CTE Recursion to get tree hierarchy](http://stackoverflow.com/questions/18106947/cte-recursion-to-get-tree-hierarchy) — Evaldas Buinauskas, Oct 21 '15 at 12:10
Instead of recursion use [hierarchyid](https://msdn.microsoft.com/en-us/library/bb677290.aspx). Recursion has the worst possible performance, equivalent to a cursor (it's not different that a cursor actually) and can't be accelerated using indexes. HierarchyID is available since SQL Server 2008 so it *is* how you handle hierarchies in general — Panagiotis Kanavos, Oct 21 '15 at 12:36
Does Entity Framework support HierarchyID? If not, I would avoid it — JamieD77, Oct 21 '15 at 15:26

Giorgi Nakeuri · Answer 1 · 2015-10-21T12:36:51.337

Here is a little example:

DECLARE @DIM_AM TABLE([AM_ID] INT, [AMI_ID] int, [Parent_AMI_ID] INT, [AMI_Code] VARCHAR(20))
DECLARE @DIM_AMI TABLE([AMI_ID] INT, [AMI_Name] VARCHAR(20))

INSERT INTO @DIM_AMI VALUES
(1, 'AMI1'),
(2, 'AMI2'),
(3, 'AMI3'),
(4, 'AMI4')

INSERT INTO @DIM_AM VALUES
(1, 1, NULL, 'CODE_AMI1'),
(2, 2, 1, 'CODE_AMI2'),
(3, 3, 1, 'CODE_AMI3'),
(4, 4, 3, 'CODE_AMI4')



;WITH cte AS(SELECT *, 0 AS Level FROM @DIM_AM WHERE Parent_AMI_ID IS NULL
             UNION ALL
             SELECT d.*, c.Level + 1 FROM cte c 
             JOIN @DIM_AM d ON c.AMI_ID = d.Parent_AMI_ID)
SELECT c.Level, c.AMI_Code, d.AMI_Name FROM cte c
JOIN @DIM_AMI d ON d.AMI_ID = c.AMI_ID

Output:

Level   AMI_Code    AMI_Name
0       CODE_AMI1   AMI1
1       CODE_AMI2   AMI2
1       CODE_AMI3   AMI3
2       CODE_AMI4   AMI4

This is recursive common table expression(cte):

;WITH cte AS(SELECT *, 0 AS Level FROM @DIM_AM WHERE Parent_AMI_ID IS NULL
             UNION ALL
             SELECT d.*, c.Level + 1 FROM cte c 
             JOIN @DIM_AM d ON c.AMI_ID = d.Parent_AMI_ID)

First part is starting point where you select top level elements(WHERE Parent_AMI_ID IS NULL):

SELECT *, 0 AS Level FROM @DIM_AM WHERE Parent_AMI_ID IS NULL

Then by syntax you need union all.

Then comes recursive part that selects children of previous select:

SELECT d.*, c.Level + 1 FROM cte c 
JOIN @DIM_AM d ON c.AMI_ID = d.Parent_AMI_ID

SQL Server 2008 has hierarchyid to handle hierarchies. Recursion and cursors are the slowest way to do this. Even before SQL Server 2008, the nested sets technique was used to avoid recursion and cursors — Panagiotis Kanavos, Oct 21 '15 at 12:38
I agree that its performance is bad. Just answered a question where recursion was asked. — Giorgi Nakeuri, Oct 21 '15 at 12:41

Recursive Function in MS SQL

1 Answers1