Post reply

Common Table Expression Misunderstandings

  • February 27, 2021 at 12:00 am

    #3850600

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

  • February 27, 2021 at 11:50 am

    #3851003

    not even if we do select top 100 percent?

  • February 27, 2021 at 1:58 pm

    #3851036

    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

  • February 27, 2021 at 3:00 pm

    #3851047

    Good point about CTE's.

  • February 28, 2021 at 8:17 am

    #3851244

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

  • February 28, 2021 at 12:59 pm

    #3851304

    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.

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

  • February 28, 2021 at 1:38 pm

    #3851310

    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 3 years, 8 months ago by  f.clems. Reason: Emoji not working

  • February 28, 2021 at 2:34 pm

    #3851322

    Common is likely because it can be used anywhere on the same query multiple times unlike a derived table/subquery.

     

  • February 28, 2021 at 4:39 pm

    #3851346

    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. 

  • February 28, 2021 at 4:47 pm

    #3851348

    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.

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

  • February 28, 2021 at 4:51 pm

    #3851349

    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.

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

  • February 28, 2021 at 5:38 pm

    #3851360

    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.

  • February 28, 2021 at 6:16 pm

    #3851365

    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.

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

  • February 28, 2021 at 7:29 pm

    #3851379

    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?

  • March 1, 2021 at 3:19 pm

    #3851761

    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.

    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