Understanding CROSS APPLY

Andy Levy has a T-SQL programming breakthrough:

Finally, this week I had a breakthrough. I was working on updating a bunch of data but it was breaking on a small subset of that data. In this case, I was attempting to JOIN two tables on fields that should have been INTs, but in a very small number of cases one side was using a comma-delimited string. The user told me that someone else had done these updates in the past and didn’t encounter the problem I was having (so I knew that it was something i was doing “wrong”), but given that it was only a handful of broken updates she was OK with manually doing the updates (we were scripting it because we were updating potentially tens of thousands of records).

I am not OK with manually fixing this in the future. I wanted to know how the other DBA had done it before. I dug into some history and found CROSS APPLY. My nemesis. I was determined to figure out how to use it this time.

The APPLY operator is extremely powerful in the right set of circumstances.  Andy shows the “classic” use case, but there are a number of other uses for the operator.

Related Posts

Calculating Relative Risk In T-SQL

Mala Mahadevan explains how to calculate relative risk using T-SQL: In this post we will explore a common statistical term – Relative Risk, otherwise called Risk Factor. Relative Risk is a term that is important to understand when you are doing comparative studies of two groups that are different in some specific way. The most […]

Read More

Cochran-Mantel-Haenszel Test

Mala Mahadevan explains the Cochran-Mantel-Haenszel test, with two parts up so far.  First, her data set: Below is the script to create the table and dataset I used. This is just test data and not copied from anywhere. Second, an introduction to the test itself and solutions in R and T-SQL: This test is an […]

Read More