the use of CTEs

Question

Post reply

the use of CTEs

Viewing 15 posts - 106 through 120 (of 161 total)

You must be logged in to reply to this topic. Login to reply

Sergiy SSC Guru Points: 110208 More actions · Answer 1

Jason A. Long (1/6/2016)
But you were 11 mins too slow... 😛

Sorry, was having lunch, and did not refresh the page before hitting "Add Reply".

Did not even bother checking email notifications...

Shame on me!

🙂

_____________
Code for TallyGenerator

Jason A. Long SSC-Insane Points: 23786 More actions · Answer 2

Sergiy (1/6/2016)
Jason A. Long (1/6/2016)
But you were 11 mins too slow... 😛
Sorry, was having lunch, and did not refresh the page before hitting "Add Reply".
Did not even bother checking email notifications...
Shame on me!
🙂

LOL... It's all in good fun. 🙂

Alan Burstein SSC Guru Points: 61141 More actions · Answer 3

.... and DelimitedSplit8K_LEAD

DECLARE

@pString varchar(8000) = 'xxx,yyy,zzz',

@delimiter char(1) = ',';

SELECT

ItemNumber = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),

Item = SUBSTRING(@pString,s.N1+1,ISNULL(NULLIF((LEAD(s.N1,1,1)

OVER (ORDER BY s.N1) - 1),0)-s.N1,8000))

FROM

(

SELECT 0 UNION ALL

SELECT TOP (DATALENGTH(ISNULL(@pString,1))) N

FROM

(

SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM

(

SELECT 1 FROM (VALUES

(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),

(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t(N) -- 90 values

) a(x),

(

SELECT 1 FROM (VALUES

(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),

(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t(N) -- 90 values

) b(x) -- 8100

) t(N)

WHERE (SUBSTRING(@pString,t.N,1) = @delimiter OR t.N = 0)

) s(N1);

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

erics44 SSCertifiable Points: 6311 More actions · Answer 4

erics44

SSCertifiable

Points: 6311

January 7, 2016 at 12:35 am

#1850114

You spoilt a good thread boys!

Sergiy SSC Guru Points: 110208 More actions · Answer 5

erics44 (1/7/2016)
You spoilt a good thread boys!

Define "spoilt".

It's all the matter of preferences, you know.

_____________
Code for TallyGenerator

erics44 SSCertifiable Points: 6311 More actions · Answer 6

Sergiy (1/7/2016)
erics44 (1/7/2016)
You spoilt a good thread boys!
Define "spoilt".
It's all the matter of preferences, you know.

ha, very true

thing is, unlike whether a CTE can spoil a nice bit of code, thread spoiling is more black and white

as in, we were having a nice debate about CTEs spoiling nice bits of code and then someone comes along and changes the subject to something completely different and the original question is lost

thread spoilt 🙂

jaime.simancas 27970 SSC Enthusiast Points: 168 More actions · Answer 7

erics44 (1/7/2016)
Sergiy (1/7/2016)
erics44 (1/7/2016)
You spoilt a good thread boys!
Define "spoilt".
It's all the matter of preferences, you know.
ha, very true
thing is, unlike whether a CTE can spoil a nice bit of code, thread spoiling is more black and white
as in, we were having a nice debate about CTEs spoiling nice bits of code and then someone comes along and changes the subject to something completely different and the original question is lost
thread spoilt 🙂

Came in to the office this AM and read all this "spoilt" stuff.

Yes, CTE's can be over used, under used. Yes, many times I've seen and heard the good and the bad about using CTE's, and through this process I read that there are people that go into the infinite detail of properly defining what CTE is, when it was created, who uses it in books and details of which I will never ever present to any VPs in the company. In the end, the realization for me is, somewhere down the line, the subject matter was changed, spoilt.!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

You have to remember that many times threads go off on tangents, and sometimes the tangents actually impart knowledge that may not have been provided otherwise.

I don't believe that this thread was spoilt at all.

erics44 SSCertifiable Points: 6311 More actions · Answer 9

Lynn Pettis (1/7/2016)
You have to remember that many times threads go off on tangents, and sometimes the tangents actually impart knowledge that may not have been provided otherwise.
I don't believe that this thread was spoilt at all.

maybe it will take off again then

Alan Burstein SSC Guru Points: 61141 More actions · Answer 10

erics44 (1/7/2016)
Lynn Pettis (1/7/2016)
You have to remember that many times threads go off on tangents, and sometimes the tangents actually impart knowledge that may not have been provided otherwise.
I don't believe that this thread was spoilt at all.
maybe it will take off again then

The interesting thing about the tangent is that an important benefit of CTE's was demonstrated. Unlike a subquery, the CTE can be referenced multiple times. If you look at the splitter examples you'll see that the "dummy rows" tables are not re-usable. For example, let's start with the tally table logic for Jason Longs CTE-less splitter:

SELECT N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

FROM

(

SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) E1 (n)

CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) E2 (n)

CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) E3 (n)

CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) E4 (n)

CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) E5 (n)

CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) E6 (n)

) DummyRows(c);

Using a CTE this could be re-written like this:

WITH

E1(N) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) E1 (n)),

E3(N) AS (SELECT 1 FROM E1 a CROSS JOIN E1 b CROSS JOIN E1 c)

SELECT N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E3 a CROSS JOIN E3 b

Jason's does not work in SQL 2005 because of the VALUES constructor but Sergiy's does. Let's look at his tally table logic:

SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

FROM

(

SELECT 1 FROM

(

SELECT 1 FROM

(

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1

) a (N) ,

(

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1

) b (N)

) a (N),

(

SELECT 1 FROM

(

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1

) a (N) ,

(

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1

) b (N)

) iTally (N);

Using a CTE and keeping the code compatible with SQL 2005, we could re-write the code like this:

WITH

E1(N) AS

(

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1

),

E3(N) AS (SELECT 1 FROM E1 a, E1 b, E1 c, E1 d)

SELECT N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

FROM E3;

Huge, Huge improvement!!! Irrefutable evidence of the usefulness of a CTE.

Just imagine trying to re-write Adam Machanic's make_parallel function without a CTE :hehe::hehe::hehe::hehe:

CREATE FUNCTION dbo.make_parallel()

RETURNS TABLE AS

RETURN

(

WITH

a(x) AS

(

SELECT

a0.*

FROM

(

VALUES (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),

(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),

(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)

) AS a0(x)

),

b(x) AS

(

SELECT TOP(9223372036854775807) 1

FROM

a AS a1,

a AS a2,

a AS a3,

a AS a4

WHERE

a1.x % 2 = 0

)

SELECT

SUM(b1.x) AS x

FROM

b AS b1

HAVING

SUM(b1.x) IS NULL

)

GO

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

jaime.simancas 27970 SSC Enthusiast Points: 168 More actions · Answer 11

Alan.B (1/7/2016)
erics44 (1/7/2016)
Lynn Pettis (1/7/2016)
You have to remember that many times threads go off on tangents, and sometimes the tangents actually impart knowledge that may not have been provided otherwise.
I don't believe that this thread was spoilt at all.
maybe it will take off again then
The interesting thing about the tangent is that an important benefit of CTE's was demonstrated. Unlike a subquery, the CTE can be referenced multiple times. If you look at the splitter examples you'll see that the "dummy rows" tables are not re-usable. For example, let's start with the tally table logic for Jason Longs CTE-less splitter:
SELECT N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM
(
SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) E1 (n)
CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) E2 (n)
CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) E3 (n)
CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) E4 (n)
CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) E5 (n)
CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) E6 (n)
) DummyRows(c);
Using a CTE this could be re-written like this:
WITH
E1(N) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) E1 (n)),
E3(N) AS (SELECT 1 FROM E1 a CROSS JOIN E1 b CROSS JOIN E1 c)
SELECT N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E3 a CROSS JOIN E3 b
Jason's does not work in SQL 2005 because of the VALUES constructor but Sergiy's does. Let's look at his tally table logic:
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM
(
SELECT 1 FROM
(
SELECT 1 FROM
(
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
) a (N) ,
(
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
) b (N)
) a (N),
(
SELECT 1 FROM
(
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
) a (N) ,
(
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
) b (N)
) b (N)
) iTally (N);
Using a CTE and keeping the code compatible with SQL 2005, we could re-write the code like this:
WITH
E1(N) AS
(
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
),
E3(N) AS (SELECT 1 FROM E1 a, E1 b, E1 c, E1 d)
SELECT N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM E3;
Huge, Huge improvement!!! Irrefutable evidence of the usefulness of a CTE.
Just imagine trying to re-write Adam Machanic's make_parallel function without a CTE :hehe::hehe::hehe::hehe:
CREATE FUNCTION dbo.make_parallel()
RETURNS TABLE AS
RETURN
(
WITH
a(x) AS
(
SELECT
a0.*
FROM
(
VALUES (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)
) AS a0(x)
),
b(x) AS
(
SELECT TOP(9223372036854775807) 1
FROM
a AS a1,
a AS a2,
a AS a3,
a AS a4
WHERE
a1.x % 2 = 0
)
SELECT
SUM(b1.x) AS x
FROM
b AS b1
HAVING
SUM(b1.x) IS NULL
)
GO

BRILLIANT!

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 12

Jason A. Long (12/24/2015)
Unless you're using recursion, a CTE can be rewritten as a derived table... Considering that derived tables (and CTEs) are processed before the outer query, CTEs can allow the code to laid out in an order that more closely resembles the actual processing order.
That said, if there isn't a logical reason to use a CTE, don't...

I'm pretty sure that CTEs aren't necessarily processed before anything else any more than a view would be processed first.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 13

Hugo Kornelis (1/6/2016)
A CTE allows you to define a complex subquery once and refer to it multiple times in the same query without repeating the code.

That's the only benefit... not having to write the code more than once. Much like a view, the entire CTE will be executed depending on how many time's it's referenced. In such cases, I've found that the "Divide'n'Conquer" method of creating a Temp Table on the fly can be very much more effective.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 14

Jeff Moden (1/7/2016)
Jason A. Long (12/24/2015)
Unless you're using recursion, a CTE can be rewritten as a derived table... Considering that derived tables (and CTEs) are processed before the outer query, CTEs can allow the code to laid out in an order that more closely resembles the actual processing order.
That said, if there isn't a logical reason to use a CTE, don't...
I'm pretty sure that CTEs aren't necessarily processed before anything else any more than a view would be processed first.

In almost every case you'd be absolutely right. There are a few exceptions, including rCTE's (as you'd expect). Even if a CTE is "used" more than once in a query, the plan will likely as not mask the CTE usage - you won't be able to tell. Derived tables too.

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 15

jaime.simancas 27970 (1/6/2016)
...is always better and faster, ...

Oh... you said "always" and that's patently not true especially if you add resource usage into the equation. For example, do a "set-based" Triangular Join to form a running total and you'll see that it's the multiple sets it creates is actually a huge problem.

I say "set-based" in quotes because, technically (to me, anyway), a set is created for each row qualifying it as RBAR on steroids.

Then there's my lovely friend known as an rCTE (Recursive CTE), especially the kind that create a sequence of values. A lot of people insist they're "set-based". I strongly disagree but let's say that they are for minute. A well formed WHILE loop can easily beat such an rCTE.

Then, there are non-recursive CTEs. Technically, a CTE that is called more than once is "set-based" but, if you do call it more than once, the entire CTE is re-executed just like a view which, technically, is a form of RBAR (1 possibly identical execution for every call).

Now, I will temper all of that by saying that, many times, what people are calling "set-based" isn't actually set-based code under the covers and that's a part of the problem that makes the "always" in your comment False depending on who you're talking to. If I were to rewrite your statement, it would be "PROPER implementation of PROPERLY written set-based code is USUALLY faster and better than non set-based code". I'd also add to it that if you haven't found a way to work your problem with PROPER set-based code, there's a very strong chance that you haven't looked hard enough. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)