200
This is pretty broad, but I'll give you as general an answer as I can.
CTEs...
Are unindexable (but can use existing indexes on referenced objects)
Cannot have constraints
Are essentially disposable VIEWs
Persist only until the next query is run
Can be recursive
Do not have dedicated stats (rely on stats on the underlying objects)
#Temp Tables...
Are ...
29
EDIT:
Please see Martin's comments below:
The CTE is not materialised as a table in memory. It is just a way of encapsulating a query definition. In the case of the OP it will be inlined and the same as just doing SELECT Column1, Column2, Column3 FROM SomeTable. Most of the time they do not get materialised up front, which is why this returns no rows ...
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]
...
27
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
As a rule, a CTE will NEVER improve performance.
A CTE is essentially a disposable view. There are no additional statistics stored, no indexes, etc. It functions as a shorthand for a subquery.
In my opinion they can be EASILY overused (I see a lot of overuse in code in my job). Some good answers are here, but if you need to refer to something more ...
24
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 ...
23
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
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 *
...
20
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 ...
17
You can only have one statement after the CTE. You can, however, define subsequent CTEs based on a previous one:
WITH t1 AS (
SELECT a, b, c
FROM table1
)
, t2 AS (
SELECT b
FROM t1
WHERE a = 5
)
SELECT *
FROM t2;
Given that you are trying to count the rows and populate a ref cursor from the same result set, it may be more appropriate to do ...
16
The accepted answer here says "a CTE should never be used for performance" - but that could mislead. In the context of CTEs versus temp tables, I've just finished removing a swathe of junk from a suite of stored procs because some doofus must've thought there was little or no overhead to using temp tables. I shoved the lot into CTEs, except those which were ...
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
A CTE may be called repeatedly within a query and is evaluated every time it is referenced - this process can be recursive. If it is just referred once then it behaves much like a sub-query, although CTEs can be parameterised.
A temporary table is physically persisted, and may be indexed. In practice the query optimiser may also persist intermediate join ...
answered Feb 15 '12 at 16:53
ConcernedOfTunbridgeWells
16.2k2 gold badges50 silver badges69 bronze badges
14
One place besides recursion where I find CTEs incredibly useful is when creating complex reporting queries. I use a series of CTEs to get chunks of the data I need and then combine in the final select. I find they are easier to maintain than doing the same thing with a lot of derived tables or 20 joins and I find that I can be more assured that it returns ...
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 ...
14
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
...
13
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 ...
13
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 "...
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
Temp table is a real object in tempdb, but cte is only a kind of wrapper around complex query to simplify syntax of organize recursion in one step.
11
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
105k11 gold badges225 silver badges354 bronze badges
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'...
11
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....
11
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 ...
11
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
...
10
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.
10
No, a CTE or with clause is defined within the scope of a single statement
Sometimes you can do more than you might expect with a single statement though, eg:
with w as (select v from t3)
insert all into t1(v) values(v)
into t2(v) values(v)
select v from w;
The 'normal' Oracle way to store temporary result sets (if you have to) is to use a GTT:
...
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 ...
Only top voted, non community-wiki answers of a minimum length are eligible
