Have you frequently tried recursion in SQL? In Transact-SQL, we can use store procedure or function to perform a recursive call like other programing language. Another way is to rely on the Common Table Expression (or CTEs) to allow a query to reference itself in the SELECT statement.
Personally, I think the CTE Recursive is simply creating an iterative to traverse your database records rather than perform a true recursion. This could be explained because SQL is theoretically a declarative query language unlikely a procedural approach in programming. In other words, we declare what we want and the database engine itself to figure out the optimized procedure.SQL Recursive is useful when working with the hierarchical or tree-based data. Several applications of hierachical data include employee hierarchy, project tasks, folder lists etc. This post will showcase the examples of SQL Recursive from fundamental recursion problems e.g. fibonacci, factorial etc.. and also the common use cases in the real applications.
1. Arithmetic sequence
2. Fibonacci sequence
3. Factorial of number N
4. Sum of digits of a number
5. Employee hierarchy
6. Project critical path and duration
7. Loan repayment
8. Shortest path using BFS algorithm
No comments:
Post a Comment