Cross-Apply

Transact-SQL
1

Hello everyone,
I've read up on cross apply, and I had some assistance with a query I've created where the person helping me added cross apply within the query. I'm trying to understand 1: what they're doing by adding this cross apply to the query and 2: the purpose for them to add this cross apply to my query. I've included the cross apply snippet to this post, but if it would be more helpful to provide the entire query, I can do that as well. Any help is greatly appreciated. Thanks!

SELECT Distinct CorpHouseCust,AcctCorp,House,Cust,WORDATE,WSTAT,OrderNo,ListItem

INTO #TMP4O

FROM #TMP3O a WITH(NOLOCK)
cross apply (select XMLEncoded=(SELECT LONGDESC + ','
FROM #TMP3O b WITH(NOLOCK)
WHERE b.corphousecust = a.corphousecust AND
b.acctcorp = a.acctcorp AND
b.house = a.house and
b.cust = a.cust AND
b.WORDATE = a.WORDATE AND
b.WSTAT = a.WSTAT AND
b.OrderNo = a.OrderNo

FOR XML PATH(''))) F0
cross apply (select LONGDESCs=''+replace(XMLEncoded,'','')+'') F1
cross apply (select XmlList=cast(LONGDESCs as xml)) F2
cross apply XmlList.nodes('x') F3(XmlNode)
cross apply (select ListItem=XmlNode.value('.','varchar(max)')) F4
2

Cross apply is sort of like an inner join (but it really is not - in the example you posted you could not have used inner join). Cross apply allows you to use the rows from the left table in the query within cross apply. In that sense, it can be thought of as evaluating the right virtual table for each row in the left table.

It would be very useful to watch this video and/or read through this article, both by Ben-Gan.

3

Me too!

Are you just trying to get a comma-delimited list of all the Child records?

That is normally done with a one-step process in a CROSS APPLY - maybe your requirements are broader though, hence the multiple CROSS APPLY's?

4

I think you really only require the first CA. You can reduce the overhead by doing DISTINCT first; and convert the xml to varchar(max) vastly more simply than using 4 additional CAs(!).

SELECT	CorpHouseCust,AcctCorp,House,Cust,WORDATE,WSTAT,OrderNo,ListItem

INTO	#TMP4O

FROM	(
    SELECT DISTINCT CorpHouseCust,AcctCorp,House,Cust,WORDATE,WSTAT,OrderNo
    FROM #TMP3O WITH(NOLOCK)
) AS  a
cross apply (select ListItem=CAST((SELECT LONGDESC + ''
FROM #TMP3O b WITH(NOLOCK)
WHERE	b.corphousecust = a.corphousecust AND
b.acctcorp = a.acctcorp AND
b.house = a.house and
b.cust = a.cust AND
b.WORDATE = a.WORDATE AND
b.WSTAT = a.WSTAT AND
b.OrderNo = a.OrderNo
FOR XML PATH('')) AS varchar(max))) F0
5

I recommend:

			FOR XML PATH(''), TYPE
		).value('.', 'varchar(max)')

otherwise embeded XML characters such as "<", ">" etc. get mucked up

For example:

SELECT	SomeParentColumns,
	, STUFF(
		(
			SELECT	',', ChildColumnToConcatenate
			FROM	MyChildTable AS C
			WHERE	C.MyChildKey = P.MyParentKey
			ORDER BY ChildSequenceColumn
			FOR XML PATH(''), TYPE
		).value('.', 'varchar(max)')
	, 1, 1, '') AS ListColumnName
FROM	MyParentTable AS P

Looks like NOLOCK is being used with a Pepper-Pot in the O/P's company - given that it is being used n a #TEMP table, only accessible to the current query. I wonder if folk at the O/P's company have any idea of how bad the side effects of NOLOCK can be?