• P74 (8/5/2014)


    Hi Lynn, I do use CTEs, but i've never specified the column names in that way... i just normally go "with myCTE as (select col1,col2 from tab1)". is this bad practice?

    thanks again guys for the tips, i found these two articles very interesting:

    http://www.sqlservercentral.com/articles/T-SQL/74118/

    http://www.sqlservercentral.com/articles/T-SQL/62867/

    cheers

    😀

    Just to be sure, that first article is about the performance pitfalls of "Recursive CTEs" (rCTE for short). Neither Lynn or I have used rCTEs. Instead, we've used versions of Itzik Ben-Gan's "Cascading CTEs", which are quite fast and resource efficient. In fact, they produce exactly ZERO reads by themselves.

    As for your question, your method of writing CTEs is just fine. Lynn and I wanted to create a column name for the constant (1) and for the final ROW_NUMBER in the CTEs. We could have done that in the SELECTs inside the CTE or like we did outside the CTE. In this case, we did it just for easier, more consistent formatting because we only need a single column alias. There is no "best practice" in this particular area. Rather, "It Depends" comes into play for many reasons.

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