Introduction to Common Table Expressions

  • I agree on readability, which is very important for maintaining code for developers.

    One item I have found is that in certain situations referencing a CTE over and over again some how slows down the query. I have found that the calls to the cteFilteredSearchList are slower then going to the table directly dbo.SearchList or a view. Just what I have found from using CTEs for search results.

    Example:

    ;with cteFilteredSearchList

    as

    (

    Select....from dbo.SearchList where Active='1'

    ),

    cteLookupByX

    as

    (

    select .... from cteFilteredSearchList where ....

    )

    cteLookupByY

    as

    (

    select .... from cteFilteredSearchList where ....

    )

    Then union the results and review what came back.


    Over 12yrs in IT and 10yrs happily stuck with SQL.
    - SQL 2008/R2/2012/2014/2016/2017
    - Oracle 8/9/10/11
    - MySQL 4/5 and MariaDB

  • Thank you. I now understand the basics of CTE's. I've seen them referenced and this article represents my first understanding. I'll reference it as I gain more knowledge.

    I can see using a CTE with UNIONS. I frequently write temp-code like this:

    Query1

    union

    Query2

    union

    Query3

    Where all three queries are similar but rather complex, but not so complex that I need a permanent view or even a temp table. With a CTE, I can maintain some readability and not have to worry about dropping the view/temp table afterward. 😉

    A fine article. Thanks again.

Viewing 2 posts - 16 through 16 (of 16 total)

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