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