Monday, August 6, 2018

Recursive CTE – Sql Server - very useful links

CTE(common table Expression)
It is a temporary named result set which can refer within a SELECT, INSERT, UPDATE, DELETE or in a CREATE VIEW statement, as part of the view’s SELECT query statement. From SQL Server 2008 on wards, it can be used with MERGE statement also.
SQL Server supports two types of CTEs-recursive and nonrecursive. A recursive CTE query must contain at least two members (statements), connected by the UNION ALL, UNION, INTERSECT, or EXCEPT operator.
SQL WITH clause

The SQL WITH clause was introduced by Oracle in the Oracle 9i release 2 database. The SQL WITH clause allows to give a sub-query block which can be referenced in several places within the main SQL query.It is used for defining a temporary relation such that the output of this temporary relation is available and is used by the query that is associated with the WITH clause.

General syntax
WITH ctealias (column_aliases)  
AS  

cte_query_definition1   --initialization 
UNION ALL 
cte_query_definition2 --recursive execution 
)  
SELECT * FROM ctealias;

http://sqlhints.com/2015/09/05/recursive-cte-sql-server/
https://www.mssqltips.com/sqlservertip//recursive-queries-using-common-table-expressions-cte-in-sql-server/

Direct recursion occurs when a method invokes itself. Indirect recursion occurs when a method invokes another method, eventually resulting in the original method being invoked again.

No comments:

Post a Comment

GEN AI

  Stay Tuned....