Common Table Expression Misunderstandings

  • Comments posted to this topic are about the item Common Table Expression Misunderstandings

  • not even if we do select top 100 percent?

  • In the first paragraph it says "Windowing functions are limited to the SELECT and OVER clause..."ย  Correction, windowing functions are permitted in both the SELECT list as well as the ORDER BY clause.ย  I'm not finding where it says this in the Docs but here's an example

    select * 
    from (values (1),(2)) v(n)
    order by row_number() over (order by v.n desc);
    n
    2
    1

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben kรถnnen

  • Good point about CTE's.

  • With such a name, CTE were kind of made for misconceptions. Could we rename them LVR (Local View with Recursion)?

  • f.clems wrote:

    With such a name, CTE were kind of made for misconceptions. Could we rename them LVR (Local View with Recursion)?

     

    BWAAAA-HAAAAA! ๐Ÿ˜€ย  Let's not because then someone would want to rename iTVFs as PDIVs (Parameter Driven Inline Views). ๐Ÿ˜€

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • Jeff Moden wrote:

    BWAAAA-HAAAAA! ๐Ÿ˜€ย  Let's not because then someone would want to rename iTVFs as PDIVs (Parameter Driven Inline Views). ๐Ÿ˜€

    (THUMBS-UP) If it helps to leave the misconception behind, I'm on it. I start a parallel rewrite of the ANSI and T-SQL documetation...

    I'll be curious to know how we ended up with this name CTE. What's so "Common" about it...

    • This reply was modified 1 month, 1 week ago by  f.clems. Reason: Emoji not working
  • Common is likely because it can be used anywhere on the same query multiple times unlike a derived table/subquery.

     

  • The name common table expression comes from the way that you factor it out of multiple positions in a query expression and move it to the front. By analogy, it's something like 4x (a + b) = (4xa + 4xb), only using set and relational operations instead of arithmetic. The full-blown ANSI version, it can be factored out of several expressions and computed only once. Good optimizers do this under the covers; we just exposed it to the programmer.

    Please post DDL and follow ANSI/ISO standards when asking for help. 

  • f.clems wrote:

    Jeff Moden wrote:

    BWAAAA-HAAAAA! ๐Ÿ˜€ย  Let's not because then someone would want to rename iTVFs as PDIVs (Parameter Driven Inline Views). ๐Ÿ˜€

    (THUMBS-UP) If it helps to leave the misconception behind, I'm on it. I start a parallel rewrite of the ANSI and T-SQL documetation...

    I'll be curious to know how we ended up with this name CTE. What's so "Common" about it...

    It's better that what they call it in Oracle... which is, "Subquery Refactoring", which is probably more accurate but doesn't exactly roll off the tongue.

     

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • jcelko212 32090 wrote:

    The name common table expression comes from the way that you factor it out of multiple positions in a query expression and move it to the front. By analogy, it's something like 4x (a + b) = (4xa + 4xb), only using set and relational operations instead of arithmetic. The full-blown ANSI version, it can be factored out of several expressions and computed only once. Good optimizers do this under the covers; we just exposed it to the programmer.

    Awesome... thanks, Joe.

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • Any thoughts on the pluses or minuses of using a temporary table versus a CTE? Would the size of the "virtual view" be a consideration.

  • dweil56 wrote:

    Any thoughts on the pluses or minuses of using a temporary table versus a CTE? Would the size of the "virtual view" be a consideration.

    Sure...

    1. Like what was written in Kathi's are said, if you call a CTE more than once in the query that's using it, it works just like a view would and is executed once each time it is called.ย  Unless it's a very quick execution, I've found it extremely helpful to remove the CTE from the code and turn the CTE code into code that populates a table.ย  It can also be extremely helpful performance-wise to either pre-build the TempTable with a Clustered Index to support the rest of the code or build one immediately after the Temp Table is populated.
    2. I've also found that forcing a "blocking operator" in the CTE (such as a sort that's not needed but also requires a large TOP) will make it so that the results of the CTE are more like a "materialized table" than a view.ย  YMMV depending on what you're doing but, in the right circumstances, it can really make a difference in performance.ย  Another blocking operator is a GROUP BY.
    3. The "size" of the virtual results of a CTE doesn't seem to matter as much as how it will be used, which is true in any query with or without a CTE.

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • jcelko212 32090 wrote:

    The name common table expression comes from the way that you factor it out of multiple positions in a query expression and move it to the front. By analogy, it's something like 4x (a + b) = (4xa + 4xb), only using set and relational operations instead of arithmetic. The full-blown ANSI version, it can be factored out of several expressions and computed only once. Good optimizers do this under the covers; we just exposed it to the programmer.

    That's really nice and quite relatable now. It also leads to a second question... which DBMS possess such good optimizer? Is SqlServer one of them?

  • f.clems wrote:

    jcelko212 32090 wrote:

    The name common table expression comes from the way that you factor it out of multiple positions in a query expression and move it to the front. By analogy, it's something like 4x (a + b) = (4xa + 4xb), only using set and relational operations instead of arithmetic. The full-blown ANSI version, it can be factored out of several expressions and computed only once. Good optimizers do this under the covers; we just exposed it to the programmer.

    That's really nice and quite relatable now. It also leads to a second question... which DBMS possess such good optimizer? Is SqlServer one of them?

    To be honest, all optimizers are absolutely fascinating.ย  To me, they're one of the earliest forms of some serious "AI".ย  I'm totally amazed that any of them work as well as they do.

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

Viewing 15 posts - 1 through 15 (of 15 total)

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