Something to Remember with APPLY

I bet y’all thought that we had completely exhausted the topic of how to use APPLY – whether it be CROSS APPLY or OUTER APPLY. Well, guess what?

The word to remember when using either of the APPLY operators is deterministic. A deterministic function, as defined by O’Reilly, “always returns the same results if given the same input values.” In other words, if I provide an input of 7, I expect to get the same answer – whether it be the “Blue,” “Adolf Hitler,” or whatever else it may be each time.

In our last post – found here – I used the following code to allow for different answers each time.

SELECT *
FROM dbo.Person1 P
OUTER APPLY (SELECT TOP 1 P.ID
	FROM dbo.Person2 P2
	WHERE LEFT(P2.LastName, 1) = LEFT(P.FirstName, 1)
	ORDER BY NEWID() DESC) Z

How I was able to accomplish that is by the ORDER BY NewID() DESC clause. The NewID() function generates a unique GUID – identified in SQL Server as the data type uniqueidentifier – which will look something like the following:

6f7f606f-9ed3-48f3-b70e-90d901591203

If you were to run the following SQL code 3, 10, or even 100 times in a row, you will get different answers each time.

SELECT NEWID()

This is one of the many ways to force SQL Server to generate a random order for a set of values by using the simple “ORDER BY NewID() DESC.”

Since, thankfully, the business people that I work with do not require, nor want, a random order to our results – since the clients may think it odd that data changes week-to-week or day-to-day – then we have to make our code be deterministic.

When using either of the APPLY operators, be sure that you have an ORDER BY within the subquery. And that it truly will allow for only one value to show within your resultset. Or you will break one of my golden rules for data:

Friends don’t let friends use non-deterministic functions – outside of demos”

And with that, my friends, we have come to the end of our series on CROSS APPLY, OUTER APPLY, and STRING_SPLIT. Until next time…