f. r
A:
First, and most importantly, the title of the question is wrong: it should be "What is?"
Q:
Can someone explain this logic for a t-sql recursive query?
I am trying to understand the following logic for the following t-sql recursive query:
WITH CTE AS
(
SELECT *, COUNT(*) OVER() AS CT
FROM dbo.tblFinalDummyTable
WHERE DummyID = @DummyID
UNION ALL
SELECT FinalList.*,
COUNT(*) OVER()
INNER JOIN CTE ON FinalList.DummyID = CTE.DummyID
)
SELECT *, CT
FROM CTE
The logic is that this is a recursive query where the output will be each parent-child node (i.e. all of the records in FinalList) and each record will have a count of all records that are parent-child of it (the CT column)
I understand the first part of the query (dbo.tblFinalDummyTable).
What I don't understand is the second part. The way I have phrased the query, it seems to be fetching all the records from tblFinalDummyTable for all of the parents and then counting the number of records in CTE for each parent-child pair. But isn't that equivalent to saying that this is a recursive query that goes one level deep with the CTE being the output?
No, it's not equivalent to a recursive query, because it still fetches all the data, only then it processes the rows and counts the result of that. This can't be equivallent to a recursive query.
A recursive query would be something like this:
select *
from table
start with dummyId = @DummyId
connect by parent_id = dummyId
.”
Such images, and people like him,
Related links:
Comments