SQL Hierarchical Queries without Recursion and Cursors (The life before CTE in SQL Server)
…And Why We Need That Now! (A Modern Use Case) Remember those old times when we had to write scripts to find the ancestors or descendants of a particular node in a hierarchical tree structure stored within the RDBMS table. Recall that scripting circus we went through using cursors and/or recursive functions, which possibly triggered various side effects and performance issues. In our case, as high traffic was expected in the application, we were totally against the use of cursors or recursions in our project back then. Still, we couldn’t find any online or offline solutions or any sort of suggestions from others for a better alternative. However, I had an intuition that it could be possible and decided to give it a try anyway. Eventually created a simple and elegant solution using a ‘while’ loop and a temporary table with identity column, which saved us a lot of time, effort and of course some extra CPU cycles. After the introduction of Common Type Expression (CTE) which was releas...