Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

CTE - Temp table - CROSS APPLY Expand / Collapse
Author
Message
Posted Thursday, April 04, 2013 2:52 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 4:30 AM
Points: 403, Visits: 744
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)

Post #1438686
Posted Thursday, April 04, 2013 3:12 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 2:40 AM
Points: 1,118, Visits: 1,573
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
Post #1438694
Posted Thursday, April 04, 2013 3:18 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 4:30 AM
Points: 403, Visits: 744
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.
Post #1438696
Posted Thursday, April 04, 2013 11:17 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 2:40 AM
Points: 1,118, Visits: 1,573
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. I take back my words. I must have been blind.
I can't believe I didn't see Information Schema written there.
Toooooo much work I guess.


Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden
Post #1439098
Posted Friday, April 05, 2013 1:07 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:20 AM
Points: 6,776, Visits: 12,879
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
Post #1439115
Posted Friday, April 05, 2013 9:32 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 7:07 AM
Points: 4,133, Visits: 5,849
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 at GMail
Post #1439297
Posted Friday, April 05, 2013 9:54 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 5:18 AM
Points: 1,659, Visits: 5,221
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


  • MMGrid Addin
  • MMNose Addin


  • 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

  • Post #1439323
    Posted Friday, April 05, 2013 10:31 AM


    SSCarpal Tunnel

    SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

    Group: General Forum Members
    Last Login: Today @ 7:07 AM
    Points: 4,133, Visits: 5,849
    ...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!!


    Best,

    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru at GMail
    Post #1439354
    Posted Friday, April 05, 2013 10:41 AM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Today @ 5:18 AM
    Points: 1,659, Visits: 5,221
    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!!

    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


  • MMGrid Addin
  • MMNose Addin


  • 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

  • Post #1439359
    Posted Friday, April 05, 2013 11:19 AM


    SSC-Addicted

    SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

    Group: General Forum Members
    Last Login: Wednesday, April 09, 2014 4:30 AM
    Points: 403, Visits: 744
    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

    Post #1439378
    « Prev Topic | Next Topic »

    Add to briefcase 12»»

    Permissions Expand / Collapse