28
I always do it when posting here or on StackOverflow because for WITH - since the keyword is overloaded - the previous command requires a terminating semi-colon. If I paste a code sample that uses a CTE, inevitably some user will paste it into their existing code, and the previous statement won't have the semi-colon. So the code breaks, and I get complaints ...
28
+1 to Erik, but wanted to add two things (which did not work well in a comment):
You don't even need to look at execution plans to see that they are ignored when not used. The following should produce a "divide by 0" error but does not due to cte2 not being selected from at all:
;WITH cte1 AS
(
SELECT 1 AS [Bob]
),
cte2 AS (
SELECT 1 / 0 AS [Err]
...
26
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 ...
25
You nearly have the answer for one of your questions already.
In the MSDN page, there is a line directly after your quote that explains this:
The basic syntax structure for a CTE is:
WITH expression_name [ ( column_name [,...n] ) ]
AS
( CTE_query_definition )
The list of column names is optional only if distinct names for all ...
21
But the execution plan for both is same as shown below:
The plans are different. One is an inner join, the other is an outer join. The results may be the same in your simple test, but the semantics are different. In more complex queries, the difference may cause more obviously different execution plans, and come with a performance impact.
There are usually ...
21
It doesn't appear that they do, but this really only applies to nested CTEs.
Create two temp tables:
CREATE TABLE #t1 (id INT);
INSERT #t1 ( id )
VALUES ( 1 );
CREATE TABLE #t2 (id INT);
INSERT #t2 ( id )
VALUES ( 1 );
Query 1:
WITH your_mom AS (
SELECT TOP 1 *
FROM #t1 AS t
),
also_your_mom AS (
SELECT TOP 1 *
FROM #t2 AS t
)
SELECT *
...
17
All statements in a CTE happen virtually at the same time. I.e., they are based on the same snapshot of the database.
The UPDATE sees the same state of the underlying table as the INSERT, which means the row with val = 1 is not there, yet. The manual clarifies here:
All the statements are executed with the same snapshot (see Chapter 13), so they cannot "...
16
This is an implementation decision. It is described in Postgres documentation, WITH Queries (Common Table Expressions).
There are two paragraphs related to the issue.
First, the reason for the observed behaviour:
The sub-statements in WITH are executed concurrently with each other and with the main query. Therefore, when using data-modifying statements ...
15
This recursive CTE (SQL Fiddle) should work with your sample:
WITH cte(ParentID) AS(
SELECT ParentID FROM @Instances WHERE [Part] = 'Rivet'
UNION ALL
SELECT i.ParentID FROM cte c
INNER JOIN @Instances i ON c.ParentID = i.InstanceID
WHERE i.ParentID > 0
)
SELECT ParentID, count(*)
FROM cte
GROUP BY ParentID
ORDER BY ParentID
;
Output
...
15
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:
WITH RECURSIVE
-- descendants
rec_d (id, name) AS
(
SELECT tree.id, tree.name FROM tree WHERE name = 'Father'
UNION ALL
SELECT tree.id, tree.name FROM rec_d, tree where tree.parent_id = rec_d.id
...
15
row_number is not deterministic if there can be ties (i.e. rows with the same PartitionField and DateField values). Any of the tied values might end up with a PartitionRowId of 1 which would presumably change the final result.
You could use rank instead of row_number but that would cause you to consider all the tied rows which may not be what you want. ...
14
Is there ANY way to make the result come up with exactly 3 distinct guids and no more? I'm hoping to be able to better answer questions in
future by including plan guides with CTE-type queries that are
referenced multiple times to overcome some SQL Server CTE quirks.
Not today. Non-recursive common table expressions (CTEs) are treated as in-line view ...
14
I think I probably meant to add that comment on the prior answer, about two separate statements. It was over a year ago, so I'm not totally sure anymore.
The wCTE based query doesn't really solve the problem it's supposed to, but upon reviewing it again over a year later I don't see the possibility of lost updates in the wCTE version.
(Note that all of ...
14
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 ...
13
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).
WITH R
AS (SELECT N,
B,
E = B,
RunningTotalE = B
FROM A
WHERE ...
12
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, *
FROM nodes
WHERE parent IS NULL
LIMIT 10
)
UNION ALL
SELECT r.depth + 1, r.path || n.node, n.*
FROM node_rec r
JOIN nodes n ON n.parent = ...
answered Apr 14 '14 at 13:57
Erwin Brandstetter
120k16 gold badges268 silver badges410 bronze badges
12
In this particular case the CTE could be replaced with a normal subselect (a derived table):
SELECT
tbl_notifications.module_name,
tbl_notifications.message,
tbl_notifications.staff_username,
tbl_notifications.date_time
FROM
(
SELECT
tbl_modules.module_code
FROM
tbl_student
INNER JOIN tbl_modules ON tbl_student....
12
That's kind of expected behaviour. CTEs are materialized but there is an exception.
If a CTE is not referenced in the parent query then it is not materialized at all. You can try this for example and it will run fine:
WITH not_executed AS (SELECT 1/0),
executed AS (SELECT 1)
SELECT * FROM executed ;
Code copied from a comment in Craig Ringer's blog ...
12
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 ...
11
This is to ensure that it is not included in any previous statements since WITH can serve a variety of purposes in T-SQL.
If it's the first statement in the batch, I don't think you need it.
11
You can't easily control the order in which SQL Server will evaluate the contents of a column that does not use the correct data type (or has mismatched precision). If you try to cast a column that is nvarchar to numeric, even if you have filters that should eliminate all non-numeric values from consideration, SQL Server can still try those first (see Erland'...
10
Try:
where exists (select * ....
where Customers.orderId = ...
or Customers.secondaryId = ...
)
Eg, if you were planning on:
where orderId in (select value from ...)
or secondaryorderid in (select value from ...)
Then you make it so that you only call your subquery once, and build your OR clause into it.
where exists (select * ...
10
A CTE can't be used as a subquery. One workaround would be:
IF EXISTS
(
SELECT 1 FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY column ORDER BY Column) AS rn
FROM table
) AS DATA
WHERE rn = 2
)
BEGIN
...
END
Another would be:
IF EXISTS (SELECT 1 FROM dbo.table GROUP BY column HAVING COUNT(*) > 1)
BEGIN
...
END
Even if your ...
10
Anecdotally, I prefer to name the columns inside the CTE instead of inside the WITH CTE (xxx) AS1 clause since you'll never inadvertently mismatch the names vs the column contents.
Take for instance the following example:
;WITH MyCTE (x, y)
AS
(
SELECT mt.y
, mt.x
FROM MySchema.MyTable mt
)
SELECT MyCTE.x
, MyCTE.y
FROM MyCTE;
What ...
10
You were on the right path and almost had it. In the select of the second section the parent column should come from the cte instead of S2 and also in the second section the join was backwards (S2.id = p.parent vs S2.parent = p.id). But that's it!
create table [table]
(
id int,
parent int
);
insert into [table] values(1,NULL),
(2,1),
(3,1),
(...
10
To expand on MguerraTorres' answer:
(Updated with the info from your secondary query)
In your first query UPDATE cte says to update the table from the CTE.
FROM cte as a says to refer to the table from the CTE as a.
So, we've referred to our CTE in two places.
What you may not realize is that a CTE is re-evaluated for each time it appears in your query, ...
10
...list of known cases where SQL Server will reliably honor the CTE as an optimization fence
Any such list would rely on observed behaviour, with no guarantee of reliability.
The SQL Server query optimizer never treats a common table expression as an optimization fence per se, though some constructions are clearly difficult to optimize across. Recursive ...
10
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
literal value.
On SQL Server 2016 or higher the hotfixes before this version are
applied ...
9
SQL Server does not guarantee the timing or number of evaluations for scalar expressions. This means that a query that might throw an error depending on the order of operations in an execution plan might (or might not) do so at runtime.
The script uses CROSS APPLY where CROSS JOIN was probably intended, but the point is that the potential number of rows ...
9
Simple mistake in aliasing cte with "a"
You should update "a" instead of updating "cte"
DECLARE @a TABLE (ID int, Value int);
DECLARE @b TABLE (ID int, Value int);
INSERT @a VALUES (1, 10), (2, 20);
INSERT @b VALUES (1, 100),(2, 200);
WITH cte AS (SELECT ID, Value
FROM @a)
UPDATE a --Changed from "UPDATE cte"
SET Value = b.Value
FROM ...
Only top voted, non community-wiki answers of a minimum length are eligible
