Common Table Expressions overuse

Question

Post reply

Common Table Expressions overuse

Ion Freeman

Right there with Babe

Points: 781
More actions
June 1, 2010 at 2:48 pm

#225210

I'm seeing a lot of code like
; with EMP_CTE(EMPID, NAME) AS (SELECT EMPID, NAME FROM EMPLOYEE WHERE STARTDATE > '4/25/1968')
SELECT EMPID, NAME FROM EMP_CTE WHERE MGRID = 4;
Apparently some developers got curious and excited about CTEs and decided to slip some into production code. I'm trying to decide if it's harmful. I'm guessing not, but I thought I'd put it out there.
Do the two WHEREs make an 'AND'? Does one of the wheres have any advantage over the other?

Viewing 4 posts - 1 through 3 (of 3 total)

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

Ben Teraberry SSChampion Points: 11845 More actions · Answer 1

You should check this in your particular environment ...

I don't think you will see a difference in the execution plan between

with EMP_CTE(EMPID, NAME)

AS

(

SELECT EMPID,

NAME

FROM EMPLOYEE

WHERE STARTDATE > '4/25/1968'

)

SELECT EMPID,

NAME

FROM EMP_CTE

WHERE MGRID = 4;

and

with EMP_CTE(EMPID, NAME)

AS

(

SELECT EMPID,

NAME

FROM EMPLOYEE

WHERE STARTDATE > '4/25/1968'

AND MGRID = 4

)

SELECT EMPID,

NAME

FROM EMP_CTE;

and

SELECT EMPID,

NAME

FROM EMPLOYEE

WHERE STARTDATE > '4/25/1968'

AND MGRID = 4

It should use the same index, even if the best index is on MGRID, even in the case of the original example and copied as my first example above.

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

WayneS SSC Guru Points: 95460 More actions · Answer 2

For the most part, CTEs are just a "pre-defined sub-query". The exception is recursive CTEs, where the CTE actually references itself.

So, your query is equal to:

SELECT EMPID, NAME

FROM (SELECT EMPID, NAME FROM EMPLOYEE WHERE STARTDATE > '4/25/1968') EMP_CTE

WHERE MGRID = 4;

IMO, using CTEs is easier to read than all the sub-queries.

An example of a recursive CTE would be (note that this will generate a max recursion limit reached error!):

WITH CTE (N) AS

(

SELECT 1

UNION ALL

SELECT N FROM CTE

)

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

FROM CTE

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Jeff Moden SSC Guru Points: 1004341 More actions · Answer 3

Ion Freeman (6/1/2010)
I'm seeing a lot of code like
; with EMP_CTE(EMPID, NAME) AS (SELECT EMPID, NAME FROM EMPLOYEE WHERE STARTDATE > '4/25/1968')
SELECT EMPID, NAME FROM EMP_CTE WHERE MGRID = 4;
Apparently some developers got curious and excited about CTEs and decided to slip some into production code. I'm trying to decide if it's harmful. I'm guessing not, but I thought I'd put it out there.
Do the two WHEREs make an 'AND'? Does one of the wheres have any advantage over the other?

Not harmful but I'd have to say that particular CTE is pretty much overly complex. bteraberry's last bit of code would be the "proper" way to do it especially for simplicity and readability sake. While CTE's can certainly make code more readable, it's gross overkill here.

--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)