44
votes
Accepted
Using a CREATE TABLE AS SELECT how do I specify a WITH condition (CTE)?
It may be awkward, but you have to move the WITH clause from the top into the query. It's a part of the statement to generate the table, and that statement comes after the CREATE TABLE, so you would ...
- 64.7k
30
votes
CTE: Get all parents and all children in one statement
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
(
...
- 98.7k
30
votes
If a CTE is defined in a query and is never used, does it make a sound?
+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 ...
- 69.5k
28
votes
Accepted
How does SQL recursion actually work?
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 ...
- 86.5k
25
votes
Accepted
Nested CTEs Returning Incorrect Results in Certain Situations
The problem isn't incorrect results, but rather non-deterministic ordering of the ROW_NUMBER window function.
I'm going to reference Itzik Ben-Gan's great article, definitely check it out in full when ...
- 29.8k
22
votes
Accepted
If a CTE is defined in a query and is never used, does it make a sound?
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 ...
21
votes
Accepted
Generate Dates between Date Ranges in mysql
I tried this solution :
WITH recursive Date_Ranges AS (
select '2018-11-30' as Date
union all
select Date + interval 1 day
from Date_Ranges
where Date < '2018-12-31')
select * from ...
- 336
16
votes
Accepted
Why am I getting nondeterministic results with the deterministic function STDEV()?
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 ...
- 20.3k
13
votes
Accepted
What Rules Determine When SQL Server Use a CTE as an "Optimization Fence"?
...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 ...
- 90.3k
13
votes
Accepted
Why doesn’t this recursive CTE with a parameter use an index when it does with a literal?
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 ...
- 90.3k
13
votes
Accepted
Do I need an explicit FOR UPDATE lock in a CTE in UPDATE?
Postgres uses a multiversion model (Multiversion Concurrency Control, MVCC).
In default READ COMMITTED isolation level, each separate query effectively sees a snapshot of the database as of the ...
- 182k
12
votes
Accepted
PostgreSQL function not executed when called from inside CTE
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 ...
- 98.7k
12
votes
Accepted
List all parents/ascendants of all nodes
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 ...
- 2,211
12
votes
Nested CTEs Returning Incorrect Results in Certain Situations
How do you expect deterministic results when the first cte (cte1) uses:
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) as RegIndex
and then that Regindex column is used in the following CTEs?
OVER (ORDER ...
11
votes
Difference between inline view and WITH clause?
Other answers cover the syntax differences pretty well so I won't go into that. Instead this answer will just cover performance in Oracle.
The Oracle optimizer may choose to materialize the results ...
- 32.6k
11
votes
Why doesn’t this recursive CTE with a parameter use an index when it does with a literal?
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 ...
- 16.5k
10
votes
Accepted
Difference between inline view and WITH clause?
There are some important differences between inline views (derived tables) and WITH clause(CTE) in Oracle. Some of them are quite universal, i.e. are applicable to other RDBMS.
WITH can be used to ...
- 9,030
10
votes
Accepted
Simple SQL CTE update
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 ...
- 11.7k
10
votes
Accepted
Caching intermediary CTEs for multiple uses
No, there is no way to cache a CTE; it will typically be executed multiple times if it is referenced multiple times. If you want to avoid this, you can cache the results using a #temp table instead.
...
- 182k
10
votes
Accepted
How can a cheap function in a SELECT make the whole query slow?
Function inlining is important, and applies here, too. Your PL/pgSQL function cannot be inlined. (Besides being overkill to even call another function for the trivial expression.) But since it's still ...
- 182k
9
votes
Accepted
CTE column caused an overflow - Order By only!
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 ...
- 90.3k
9
votes
Accepted
Top Level Union Error With CTE
The error is due to this CTE:
WITH Sixer As
(
Select
DISTINCT(Doctor) As Doctor
,Practice
,AnnRev
FROM Sixer -- the error occurs here
)
You have a table named Sixer and ...
- 98.7k
9
votes
Simple SQL CTE update
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, ...
- 2,244
9
votes
Accepted
Conditional INSERT with a nested CTE?
For the purpose of this question, I'll assume employee_details.name to be defined UNIQUE. Else, the whole operation wouldn't make sense.
You cannot nest a data-modifying CTE like you tried (as you ...
- 182k
9
votes
Accepted
CTE Error when installing sp_WhoIsActive
I can repro this issue by going into connection properties and setting Column Encryption Setting=Enabled (or just ticking the Enable Always Encrypted box)
I suggest removing this to allow the ...
- 86.5k
7
votes
Accepted
Postgres WITH not performed first
Since public.samples_exp_unit and public.sample_results are the views, you have added a level of indirection in your queries.
You can rewrite to be simpler:
WITH
eu AS (TABLE public....
7
votes
Accepted
How does this derived table with cross apply work?
What you`re seeing is the XQuery implementation in SQL Server . Although XQuery uses its own parser and performs its own algebrarization during the query compilation stage, the results are combined ...
- 1,064
7
votes
Accepted
Why is Postgres CTE slower than subquery?
CTE's in PostgreSQL are an optimization fence. That means the query planner doesn't push optimizations across a CTE boundary.
Blog entry about this
I think a lot of this is silly though you can just ...
- 64.7k
7
votes
SQL Server: Improve CTE performance with case statements
I think you might be writing code under a faulty assumption: CTE results are persisted
They're not, and every time you reference them, the syntax is re-executed.
Here's a quick example:
CREATE ...
Only top scored, non community-wiki answers of a minimum length are eligible