Jeff Moden (7/29/2015)

---

I got off work just about an hour ago (9PM) and I've been looking at this code and I have to admit a bit of confusion. I might just be tired or I may have spoken a bit too soon.

1. Is this function going to be called for every NetworkItemID in the Network table on every 3AM run?

2. And, correct me if I'm wrong... the first query appears to only get one level deep from the provided @NetworkItemID and only for those rows that have a ContentItemTypeID = 2. Is that correct?

3. From there, the next two nearly identical queries seem to shift gears by getting data by ContentItemId and, unless that's a rather unique value, the rows returned will simply explode because of possible 1 to many joins.

My original thought was to do something like what I have in one of the other following articles to resolve the hierarchy but your hierarchy is only one level deep for each node and has a rather explosive set of rows for each node.

http://www.sqlservercentral.com/articles/Hierarchy/94040/

http://www.sqlservercentral.com/articles/T-SQL/94570/

Shifting gears a bit, Wayne is correct and you don't really need a table valued function. The first query in the function could be written as a CTE, the second as a "cascading" CTE (reads from the first as if it were a table valued function) and the final query could simply be another cascading CTE off that. Since it can all be written in a single query, it's like a view, which is one of the qualifiers for turning this into a high performance Inline Table Valued Function. Of course, the nested function is already in the proper form for a conversion to an iTVF.

Like I said, apologies for not truly understanding what this is doing. My brain is just a little fried.