Common Table Expressions overuse

  • 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?

  • 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]

  • 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

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

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

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