SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


CTE - Temp table - CROSS APPLY


CTE - Temp table - CROSS APPLY

Author
Message
Arjun Sivadasan
Arjun Sivadasan
Say Hey Kid
Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)

Group: General Forum Members
Points: 670 Visits: 976
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)


vinu512
vinu512
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1687 Visits: 1625
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 ;-)
Arjun Sivadasan
Arjun Sivadasan
Say Hey Kid
Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)

Group: General Forum Members
Points: 670 Visits: 976
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.
vinu512
vinu512
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1687 Visits: 1625
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 ;-)
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16222 Visits: 19545
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
Exploring Recursive CTEs by Example Dwain Camps
TheSQLGuru
TheSQLGuru
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12411 Visits: 8548
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
mister.magoo
mister.magoo
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4116 Visits: 7865
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
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • TheSQLGuru
    TheSQLGuru
    SSChampion
    SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

    Group: General Forum Members
    Points: 12411 Visits: 8548
    ...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
    mister.magoo
    mister.magoo
    SSCarpal Tunnel
    SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

    Group: General Forum Members
    Points: 4116 Visits: 7865
    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
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Arjun Sivadasan
    Arjun Sivadasan
    Say Hey Kid
    Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)

    Group: General Forum Members
    Points: 670 Visits: 976
    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


    Go


    Permissions

    You can't post new topics.
    You can't post topic replies.
    You can't post new polls.
    You can't post replies to polls.
    You can't edit your own topics.
    You can't delete your own topics.
    You can't edit other topics.
    You can't delete other topics.
    You can't edit your own posts.
    You can't edit other posts.
    You can't delete your own posts.
    You can't delete other posts.
    You can't post events.
    You can't edit your own events.
    You can't edit other events.
    You can't delete your own events.
    You can't delete other events.
    You can't send private messages.
    You can't send emails.
    You can read topics.
    You can't vote in polls.
    You can't upload attachments.
    You can download attachments.
    You can't post HTML code.
    You can't edit HTML code.
    You can't post IFCode.
    You can't post JavaScript.
    You can post emoticons.
    You can't post or upload images.

    Select a forum

































































































































































    SQLServerCentral


    Search