CTE - Temp table - CROSS APPLY

  • I had some free time at work and was trying out some silly queries when I came across this.

    CTE referenced in a subquery does not yield any result. I didn't expect this but I can understand why it can happen. Why doesn't the parser throw an error in that case?

    Here are the queries:

    --Subquery with CTE - was surprised by the output

    ;WITH cteProcDependencies

    AS

    (

    SELECT

    TOP 10 r.ROUTINE_NAME, rdep.ROUTINE_NAME [enc_routine_name]

    FROM

    INFORMATION_SCHEMA.routines r

    INNER JOIN INFORMATION_SCHEMA.routines rdep

    ON r.ROUTINE_DEFINITION LIKE '%' + rdep.ROUTINE_NAME + '%'

    AND r.ROUTINE_NAME != rdep.ROUTINE_NAME

    )

    SELECT

    pd.ROUTINE_NAME,

    STUFF((SELECT ', ' + enc_routine_name FROM cteProcDependencies WHERE ROUTINE_NAME = pd.ROUTINE_NAME ORDER BY ROUTINE_NAME FOR XML PATH('')),1,1,'') [enc_ROUTINE_NAME]

    FROM cteProcDependencies pd

    GROUP BY pd.ROUTINE_NAME

    --Subquery With Temp table

    IF OBJECT_ID('tempdb..#cteProcDependencies') IS NOT NULL

    DROP TABLE #cteProcDependencies

    GO

    SELECT

    TOP 10 r.ROUTINE_NAME, rdep.ROUTINE_NAME [enc_routine_name] INTO #cteProcDependencies

    FROM

    INFORMATION_SCHEMA.routines r

    INNER JOIN INFORMATION_SCHEMA.routines rdep

    ON r.ROUTINE_DEFINITION LIKE '%' + rdep.ROUTINE_NAME + '%'

    AND r.ROUTINE_NAME != rdep.ROUTINE_NAME

    SELECT

    pd.ROUTINE_NAME, STUFF((SELECT ', ' + enc_routine_name FROM #cteProcDependencies WHERE ROUTINE_NAME = pd.ROUTINE_NAME ORDER BY ROUTINE_NAME FOR XML PATH('')),1,1,'') [enc_ROUTINE_NAME]

    FROM #cteProcDependencies pd

    GROUP BY pd.ROUTINE_NAME

    --With cross apply, as my teammate suggested

    ;WITH cteProcDependencies

    AS

    (

    SELECT

    TOP 10 r.ROUTINE_NAME, rdep.ROUTINE_NAME [enc_routine_name]

    FROM

    INFORMATION_SCHEMA.routines r

    INNER JOIN INFORMATION_SCHEMA.routines rdep

    ON r.ROUTINE_DEFINITION LIKE '%' + rdep.ROUTINE_NAME + '%'

    AND r.ROUTINE_NAME != rdep.ROUTINE_NAME

    )

    SELECT

    pd.ROUTINE_NAME,

    tab.c [enc_ROUTINE_NAME]

    FROM cteProcDependencies pd

    CROSS APPLY (SELECT enc_routine_name FROM cteProcDependencies WHERE ROUTINE_NAME = pd.ROUTINE_NAME) [Tab] (c)

    https://sqlroadie.com/

  • Hi Arjun,

    It is good that you are posting such stuff....people can learn from this.

    But a lot of people who are here won't be able to understand things just by looking at the code.

    So, when you post something like this, you should post DDL and sample data with it as well.....so that people can test the query on the sample data and see what actually is happening.

    Learning is fun...buddy...with a little help. 🙂

    Edit: That could be too much work, i know. In that case you can use the tables in the "Adventure Works" sample database.....almost everyone has that.

    Vinu Vijayan

    For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

  • Hey, I'm not querying any user tables to post DDL. You can run the queries I posted on any database.

    Edit: I guess you didn't know that information_schema.routines was a system view.

    https://sqlroadie.com/

  • Arjun Sivadasan (4/4/2013)


    Hey, I'm not querying any user tables to post DDL. You can run the queries I posted on any database.

    Edit: I guess you didn't know that information_schema.routines was a system view.

    This is totally embarrassing. :blush: I take back my words. I must have been blind. :crazy: :crazy:

    I can't believe I didn't see Information Schema written there.

    Toooooo much work I guess. :sick:

    Vinu Vijayan

    For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

  • Arjun Sivadasan (4/4/2013)


    I had some free time at work and was trying out some silly queries when I came across this.

    CTE referenced in a subquery does not yield any result. I didn't expect this but I can understand why it can happen. Why doesn't the parser throw an error in that case?

    Here are the queries:

    --Subquery with CTE - was surprised by the output

    ;WITH cteProcDependencies

    AS

    (

    SELECT

    TOP 10 r.ROUTINE_NAME, rdep.ROUTINE_NAME [enc_routine_name]

    FROM

    INFORMATION_SCHEMA.routines r

    INNER JOIN INFORMATION_SCHEMA.routines rdep

    ON r.ROUTINE_DEFINITION LIKE '%' + rdep.ROUTINE_NAME + '%'

    AND r.ROUTINE_NAME != rdep.ROUTINE_NAME

    )

    SELECT

    pd.ROUTINE_NAME,

    STUFF((SELECT ', ' + enc_routine_name FROM cteProcDependencies WHERE ROUTINE_NAME = pd.ROUTINE_NAME ORDER BY ROUTINE_NAME FOR XML PATH('')),1,1,'') [enc_ROUTINE_NAME]

    FROM cteProcDependencies pd

    GROUP BY pd.ROUTINE_NAME

    --Subquery With Temp table

    IF OBJECT_ID('tempdb..#cteProcDependencies') IS NOT NULL

    DROP TABLE #cteProcDependencies

    GO

    SELECT

    TOP 10 r.ROUTINE_NAME, rdep.ROUTINE_NAME [enc_routine_name] INTO #cteProcDependencies

    FROM

    INFORMATION_SCHEMA.routines r

    INNER JOIN INFORMATION_SCHEMA.routines rdep

    ON r.ROUTINE_DEFINITION LIKE '%' + rdep.ROUTINE_NAME + '%'

    AND r.ROUTINE_NAME != rdep.ROUTINE_NAME

    SELECT

    pd.ROUTINE_NAME, STUFF((SELECT ', ' + enc_routine_name FROM #cteProcDependencies WHERE ROUTINE_NAME = pd.ROUTINE_NAME ORDER BY ROUTINE_NAME FOR XML PATH('')),1,1,'') [enc_ROUTINE_NAME]

    FROM #cteProcDependencies pd

    GROUP BY pd.ROUTINE_NAME

    --With cross apply, as my teammate suggested

    ;WITH cteProcDependencies

    AS

    (

    SELECT

    TOP 10 r.ROUTINE_NAME, rdep.ROUTINE_NAME [enc_routine_name]

    FROM

    INFORMATION_SCHEMA.routines r

    INNER JOIN INFORMATION_SCHEMA.routines rdep

    ON r.ROUTINE_DEFINITION LIKE '%' + rdep.ROUTINE_NAME + '%'

    AND r.ROUTINE_NAME != rdep.ROUTINE_NAME

    )

    SELECT

    pd.ROUTINE_NAME,

    tab.c [enc_ROUTINE_NAME]

    FROM cteProcDependencies pd

    CROSS APPLY (SELECT enc_routine_name FROM cteProcDependencies WHERE ROUTINE_NAME = pd.ROUTINE_NAME) [Tab] (c)

    Without ORDER BY, TOP n instructs SQL Server to return n rows...any n rows. Reference the same CTE twice in the same query (a CTE is just an inline view, remember) and you're reading the table source twice. There's no guarantee that the same TOP 10 rows will be returned by each read.

    This is a really nice compact little example, thanks for posting.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • This is an INCREDIBLY COMMON misunderstanding I see at clients all the time. You simply CANNOT rely on ORDER of data unless it is EXPLICITLY stated with an ORDER BY at the level you expect/require ordering!!! Common places I see this is INSERTing data into a temp table and then SELECTing it back out - people expect it back in the same row order "put in". The other is when there is a CLUSTERed INDEX on a table - they feel that the data will ALWAYS SELECT back out in CLUSTERED INDEX order, but that too is NOT a valid assumption.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • Agreed about the lack of ORDER BY.

    However, luck would have it that it returns data for me, as long as I run it in a database that returns data for this:

    SELECT

    TOP 10 r.ROUTINE_NAME, rdep.ROUTINE_NAME [enc_routine_name]

    FROM

    INFORMATION_SCHEMA.routines r

    INNER JOIN INFORMATION_SCHEMA.routines rdep

    ON r.ROUTINE_DEFINITION LIKE '%' + rdep.ROUTINE_NAME + '%'

    AND r.ROUTINE_NAME != rdep.ROUTINE_NAME

    Now, although the lack of ORDER BY means you can't rely on the output, I would hope that the two executions of the CTE happening together in milliseconds would return the same 10 rows, after all the order they are returned in is not randomised, it is just not user predictable...

    MM



    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
  • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • [/url]
  • How to Post Performance Problems - by Gail Shaw
  • [/url]

  • ...I would hope that the two executions of the CTE happening together in milliseconds would return the same 10 rows...

    Personally I prefer to not rely on "hope" to get the correct results from my data processing efforts!! :w00t:

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • TheSQLGuru (4/5/2013)


    ...I would hope that the two executions of the CTE happening together in milliseconds would return the same 10 rows...

    Personally I prefer to not rely on "hope" to get the correct results from my data processing efforts!! :w00t:

    Yes quite true. I guess what I should have said is that for testing purposes I would expect to return the same ten rows, but absolutely not 4 production use

    MM



    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
  • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • [/url]
  • How to Post Performance Problems - by Gail Shaw
  • [/url]

  • Folks, thanks for the replies. I checked the plan of these queries and figured out that no matching rows were being returned. It didn't dawn on me that I was using top without order by. Does the following query give non-null values for enc_routine_name column?

    PS: I do not have access to SSMS right now.

    --Subquery with CTE - was surprised by the output

    ;WITH cteProcDependencies

    AS

    (

    SELECT

    TOP 10 r.ROUTINE_NAME, rdep.ROUTINE_NAME [enc_routine_name]

    FROM

    INFORMATION_SCHEMA.routines r

    INNER JOIN INFORMATION_SCHEMA.routines rdep

    ON r.ROUTINE_DEFINITION LIKE '%' + rdep.ROUTINE_NAME + '%'

    AND r.ROUTINE_NAME != rdep.ROUTINE_NAME

    order by r.ROUTINE_NAME, rdep.ROUTINE_NAME

    )

    SELECT

    pd.ROUTINE_NAME,

    STUFF((SELECT ', ' + enc_routine_name FROM cteProcDependencies WHERE ROUTINE_NAME = pd.ROUTINE_NAME ORDER BY ROUTINE_NAME FOR XML PATH('')),1,1,'') [enc_ROUTINE_NAME]

    FROM cteProcDependencies pd

    GROUP BY pd.ROUTINE_NAME

    https://sqlroadie.com/

  • Two queries that do not have "ORDER BY" running at the about the same time might give results in the same order but might not. For example: the order of data returned can depend upon the query plan chosen which can vary by the memory available to the query which varies from instant to instant.

  • Viewing 11 posts - 1 through 11 (of 11 total)

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