CTE - Temp table - CROSS APPLY

Question

Post reply

CTE - Temp table - CROSS APPLY

Arjun Sivadasan

SSCertifiable

Points: 5902
More actions
April 4, 2013 at 2:52 am

#394520

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/
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]

vinu512 SSCoach Points: 15729 More actions · Answer 1

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] 😉

Arjun Sivadasan SSCertifiable Points: 5902 More actions · Answer 2

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/

vinu512 SSCoach Points: 15729 More actions · Answer 3

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] 😉

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 5

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 SSC-Forever Points: 47174 More actions · Answer 6

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 7

...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 SSC-Forever Points: 47174 More actions · Answer 8

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

Arjun Sivadasan SSCertifiable Points: 5902 More actions · Answer 9

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/

jbwa SSC Eights! Points: 843 More actions · Answer 10

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.