Sergiy (3/17/2013)
Lynn Pettis (3/17/2013)
Really, CTEs are complex? I find them to make writing queries easier as you don't have to write derived tables.🙂
Let's compare:
;WITH MyData AS (SELECT TOP 2 *
FROM dbo.NFFeeds
ORDER BY id DESC
)
SELECT * FROM MyData
ORDER BY id
VS.
SELECT * FROM (
SELECT TOP 2 *
FROM dbo.NFFeeds
ORDER BY id DESC) MyData
ORDER BY id
Derived table wins as it does not contain extra words ";WITH MyData AS ".
Apparently, CTE's are more complex comparing to derived tables.
😎
I will have to disagree with you. You are saying having to type "WITH MyData as " makes the query complex. Not the case. Take a look at the following actual execution plan and tell me if one is more complex than other.
Here is the quick DDL and DML I used to create the execution plans:
create table dbo.NFFeeds (
id int,
datacol varchar(10));
insert into dbo.NFFeeds
values (1,'A'),(2,'B'),(3,'C');