Earlier this week I posted The many uses of CROSS APPLY and I’m quite glad I did. I’ve been working on a series of audit queries for the last couple of weeks and got thrown a bit of a curve today. The original specs included several comparisons against a date. Basically the following.

WHERE (date1 >= '5/31/2008' AND date2 >= '5/31/2008')
OR (date3 >= '5/31/2008' AND date4 >= '5/31/2008')

I was a little shocked when I was told several weeks into the project “Next month it will change to June 30 2008.” So in reality rather than a constant (used no less than 7 times throughout each of 13 views) I now needed a calculation.

Not really a huge issue. It’s a simple enough calculation and it would be simple enough to just copy and paste it everywhere it’s needed. I just really don’t want the repetitive code. Not to mention adding that much more complexity to the views. And I shudder to think of having to change it in over 90 different places if the business specs change. So the next thought I had was using a variable, unfortunately these have to be views, although I guess I could have used TVFs (table valued functions) but still not the best solution.

Now recently I watched Kendra Little’s 5 T-SQL Features You’re Missing Out On and learned that you could use CROSS APPLY for reusable calculations. Well that’s what this is right? A reusable calculation.

So now instead of looking like this: (The calculation is from one of Pinal Dave’s blogs, and the calculation is for September 2003. If you are reading this past May 2013 that will have changed.)

SELECT *
FROM Sales.SalesOrderHeader
WHERE (DueDate >= DATEADD(mm, DATEDIFF(mm,0,GETDATE())-116,0) 
AND DueDate < DATEADD(mm, DATEDIFF(mm,0,GETDATE())-115,0))
   OR (ShipDate >= DATEADD(mm, DATEDIFF(mm,0,GETDATE())-116,0) 
AND ShipDate < DATEADD(mm, DATEDIFF(mm,0,GETDATE())-115,0))

It now instead looks like this.

SELECT *
FROM Sales.SalesOrderHeader
CROSS APPLY (SELECT DATEADD(mm, DATEDIFF(mm,0,GETDATE())-116,0) AS StartDate, 
DATEADD(mm, DATEDIFF(mm,0,GETDATE())-115,0) AS EndDate) AS Vars
WHERE (DueDate >= Vars.StartDate AND DueDate < Vars.EndDate)
   OR (ShipDate >= Vars.StartDate AND ShipDate < Vars.EndDate)

Personally I find this much easier to read. Of course if the performance isn’t there then it doesn’t really matter. Fortunately or unfortunately depending on how you look at it, all of my tests so far have been inconclusive. The query plans are very slightly different on some queries and the same on others. The run times are only slightly different and neither one runs faster every time. Until I see something conclusive one way or the other I’ll consider performance close enough with a big edge on readability and maintainability going to the CROSS APPLY.

Filed under: Microsoft SQL Server, SQLServerPedia Syndication, T-SQL Tagged: code language, CROSS APPLY, language sql, microsoft sql server, sql statements, T-SQL