The interview asked the question: what is the use of cursors when working with hierarchical structures?

Prior to this, it is necessary to say a lot with hierarchies did not have to work. There are a couple of “parent-child” tables in the project, but they are just two-level.

And now I decided to take up a more detailed study of hierarchies and cursors . But somehow, I would not look for it and no matter how big the data would be, programmers write on the forums that, regarding the parent-child structure, everything works well with рекурсии - обобщенные табличные выражения (CTE) . And there is no need to resort to cursors.

I haven’t detailed the hierarchyid data type yet, but in my opinion there’s also no point in looking for the advantages of cursors. With a superficial study of this type, as far as I understand it, I generally have my own statistical methods for working with nodes.

I thought maybe there would be some advantage found by the client application, and not the database administrator. But Google did not give anything. And in the literature that I have about cursors do not speak at all or one page material. MSDN and Intuit are more or less detailed about this, but again, there is nothing about the work of cursors with hierarchies.

I am sure that this advantage does not concern performance , here is something else).

Does anyone have any thoughts on this?

  • one
    cursors are a crutch, they do not have and there can be no advantages :) (yes, my project actively uses hierarchies, nesting is more than 2) - PashaPash ♦
  • 2
    By the way, did you try to find out the correct answer from the one who interviewed you? - PashaPash ♦
  • one
    and the tags C # and orm have something to do with the subject matter? - Anatol
  • 2
    CTEs are good at working with hierarchy, hierarchyid - it seemed to me brake (I used them in SQL Server 2012-2014). The cursor has a pair of chips - the first is that you can run stored procedures inside the cursor, the second is that, based on the current record, you can change other rows in the same table on which the cursor is used. These chips are VERY rare, but still necessary. - gofr1
  • one
    @ gofr1 the only problem is that such operations do not guarantee integrity. and they can most often be rewritten to ordinary updates with window functions. except for some very tricky cases. - PashaPash ♦

1 answer 1

I managed to get an answer from the interviewer, about what is the advantage of cursors in hierarchical structures?

Here is the answer-quote (I highlighted the text in bold):

Cursors are very expensive, resource-consuming. And they need to be applied carefully . However, there are cases when their use is justified. These cases are related to working with hierarchical data structures in a regular relational SQL database.

https://www.opennet.ru/docs/RUS/hierarchical_data/

Check out the link provided. Notice how the movement and removal of branches in trees, given in various ways.

Please look carefully at part 2 , where cursors are used to change the tree structure .

  • It was not worth posting it as an answer, and especially noting it as accepted. In the comments to the question you clearly wrote that there are more modern and convenient tools for working with hierarchical data. Recursive queries / CTE appeared in the 2008th SQL Server, three years before the article was released. Maybe your interviewer didn’t know about CTE. I will try to arrange in the form of a full answer, with examples "from the article" vs. "how to". - PashaPash ♦
  • one
    By the way, if you look carefully, the article uses cursors for working with the materialized path. and from the same year 2008, hierarchyid appeared in SQL Server, which is actually the standard implementation of the materialized path without cursors :) - PashaPash ♦
  • @PashaPash I will try to arrange in the form of a full answer, with examples "from the article" vs. "as it should . " I would be thankful. - Adam
  • By the way, he was aware of hirarchyId - he later noted this at the end: What I was waiting for (hearing from me) is the mention of hierarchyId, such as the data that appeared in SQL 2008. Using hierarchyId can significantly simplify the algorithms for the materialized path . Well, actually like you (@PashaPash) and said :). - Adam