The BOL description of recursive CTEs describes the semantics of recursive execution as being as follows:
Split the CTE expression into anchor and recursive members.
Run the anchor member(s) creating the first invocation or base result set (T0).
Run the recursive member(s) with Ti as an input and Ti+1 as an output.
Repeat step 3 until an empty set is ...
As I read the question, the basic recursive algorithm required is:
Return the row with the earliest date in the set
Set that date as "current"
Find the row with the earliest date more than 90 days after the current date
Repeat from step 2 until no more rows are found
This is relatively easy to implement with a recursive common table expression.
You need a recursive CTE (common table expression):
with -- recursive
-- some DBMS (e.g. Postgres) require the word "recursive"
-- some others (Oracle, SQL-Server) require omitting the "recursive"
-- and some (e.g. SQLite) don't bother, i.e. they accept both
(parent, descendant, lvl)
( select parent, child, 1
This recursive CTE (SQL Fiddle) should work with your sample:
WITH cte(ParentID) AS(
SELECT ParentID FROM @Instances WHERE [Part] = 'Rivet'
SELECT i.ParentID FROM cte c
INNER JOIN @Instances i ON c.ParentID = i.InstanceID
WHERE i.ParentID > 0
SELECT ParentID, count(*)
GROUP BY ParentID
ORDER BY ParentID
If you want all ancestors and all descendants, you can combine the two queries in one. Use the two CTEs and then a simple UNION:
rec_d (id, name) AS
SELECT tree.id, tree.name FROM tree WHERE name = 'Father'
SELECT tree.id, tree.name FROM rec_d, tree where tree.parent_id = rec_d.id
It is great that you are taking the time to understand, classify and model the data you are dealing with since, from my personal experiencie, all this makes the whole development process easier and very flexible for future changes. And I am quite sure that you are also aware of this already.
Preliminary data model and assumed business rules
I defined a list ...
The query you have is basically correct. The only mistake is in the second (recursive) part of the CTE where you have:
INNER JOIN descendants d ON d.parent_id = o.object_id
It should be the other way around:
INNER JOIN descendants d ON d.object_id = o.parent_id
You want to join the objects with their parents (that have already been found).
So the query ...
You need an extra column to carry along the running total (fiddle).
In the recursive part of the CTE below R refers to the "previous" row and A the current one so referencing the column from R is your SUM(E1, E2, ... En-1).
AS (SELECT N,
E = B,
RunningTotalE = B
An array representing the path from the root up to the leaf should achieve the desired sort order:
WITH RECURSIVE node_rec AS (
(SELECT 1 AS depth, ARRAY[node] AS path, *
WHERE parent IS NULL
SELECT r.depth + 1, r.path || n.node, n.*
FROM node_rec r
JOIN nodes n ON n.parent = ...
This is most easily implemented in SQL Server using a Recursive Common Table Expression.
DECLARE @binaryUser AS TABLE
id integer NOT NULL,
joiningDate date NOT NULL,
placement char(1) NOT NULL,
pId integer NOT NULL,
cId integer NOT NULL,
referBy integer NOT NULL
Randi Vertongen's answer correctly addresses how you can get the plan you want with the parameterized version of the query. This answer supplements that by addressing the title of the question in case you are interested in the details.
SQL Server rewrites tail-recursive common table expressions (CTEs) as iteration. Everything from the Lazy Index Spool down ...
If your queries have a common shape, you might be able to add the required maxrecursion hint using one or more plan guides.
There can be a knack to getting them right. If you add specific query details to your question, we might be able to work that out for you. Typically, you would trace the SQL actually hitting the server, or obtain a parameterized form ...
Though at the moment I don't have the title of the actual hotfix, the better query plan will be used when enabling the query optimizer hotfixes on your version (SQL Server 2012).
Some other methods are:
Using OPTION(RECOMPILE) so the filtering happens earlier, on the
On SQL Server 2016 or higher the hotfixes before this version are
Since this is a SQL Server 2014 question I might as well add a natively compiled stored procedure version of a "cursor".
Source table with some data:
create table T
TheDate datetime primary key
insert into T(TheDate) values
If you have absolutely have to use a function (a limitation of your ETL tool as you imply), you can specify OPTION as part of a multi-statement table-valued function, eg something like this:
CREATE FUNCTION dbo.udf_MyFunction ( @StartID INT )
RETURNS @tv TABLE
WITH Episodes( xlevel, PersonID, EventID, EpisodeID, StartDT, EndDT ) AS ...
First off, you do not want to use char(50). Use varchar(50) or just text. Read more:
Any downsides of using data type “text” for storing strings?
Assuming the following rules:
Basic slugs never end with a dash.
Duplicate slugs are suffixed with a dash and a sequential number (-123).
Note that all of the following methods are subject to a race conditions: ...
One of the key parts of Jack Douglas's solution in Group by array overlapping is the | (pipe) operator used on arrays in the recursive part of the recursive t CTE like this:
select t.id, a.id, t.clst | a.clst
This operator concatenates two arrays suppressing duplicate items. The reason that answer cannot be directly applied to your setup is because ...
Why does Recursive CTE estimate just 1 row?
Cardinality estimation for recursive common table expressions is extremely limited.
Under the original cardinality estimation model, the estimate is a simple sum of the cardinality estimates for the anchor and recursive parts. This is equivalent to assuming the recursive part is executed exactly once.
In SQL ...
You need a Window Function indeed. However LAG is not the right one. SUM(...) OVER(...) is the one you want. See SQL Fiddle.
SELECT account_id, activity_date, amount
, SUM(amount) OVER(PARTITION BY account_id ORDER BY activity_date) as balance
FROM transfers t
ORDER BY account_id, activity_date DESC;
account_id | activity_date ...
There are a few options that I was able to get working. All of the options deal with variations of filter predicates. NOTE: you must disable the Server Audit in order to make changes, and then re-enable it.
First, the most generic approach is to filter out all Scalar UDFs. You can do that by using the class_type audit field. The documentation indicates that ...
The anchor part of the recursive SQL query produces all 7488 rows. On my machine, that part of the query finishes in under 100 ms. SSMS does not immediately show all 7488 rows in the grid results. It only shows 7480 for me as well. I suspect this happens because the results are sent in packets and the remaining 8 rows aren't enough to fill a packet.
A very primitive implementation:
It basically divides the problem into two subproblems:
First find all the ancestors of the node in question (including the node itself). If the node has no parents, then this would be just itself.
Then find the descendants of all those ancestors (including themselves). We may have several nodes in the ancestors result set, ...
Recursive queries cannot be done with pure SQL when it comes to MySQL.
I have written posts on how to use Stored Procedures to accomplish this
Oct 24, 2011 : Find highest level of a hierarchical field: with vs without CTEs
Dec 10, 2012 : MySQL: Tree-Hierarchical query
Jul 15, 2013 : Get top most parent by nth child id?
Give them a Try !!!
NOTE : This ...
Here is a recursive CTE solution using a technique that Paul White blogged about in Performance Tuning the Whole Query Plan.
declare @T table
Eventdate date index IX_Eventdate clustered,
insert into @T(Eventdate, Val) values
A solution that uses a cursor.
(first, some needed tables and variables):
-- a table to hold the results
DECLARE @cd TABLE
( TheDate datetime PRIMARY KEY,
Qualify INT NOT NULL
-- some variables
@Qualify INT = 0,
@PreviousCheckDate DATETIME = '1900-01-01 00:00:00' ;
The actual cursor:
To count all children, there is no way around following each path to the last element. You can then show rows down to some depth while counting the rest:
WITH RECURSIVE cte AS (
SELECT id, parent_id, content, array[id] AS path
WHERE id = 1
SELECT c.id, c.parent_id, c.content, cte.path || c.id
FROM comment c
The technique to use is called a recursive common table expression, or recursive CTE. The Microsoft SQL documentation is comprehensive and there are a lot of third-party blog posts to help explain.
Here's an implementation for your specific question.
This part just creates the sample data from your question. It uses a temporary table (one that uses the # ...
This is just a (semi) educated guess, and is probably completely wrong. Interesting question, by the way.
T-SQL is a declarative language; perhaps a recursive CTE is translated into a cursor-style operation where the results from the left side of the UNION ALL is appended into a temporary table, then the right side of the UNION ALL is applied to the values ...