THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Rob Farley

- Owner/Principal with LobsterPot Solutions (a MS Gold Partner consulting firm), Microsoft Certified Master, Microsoft MVP (SQL Server), APS/PDW trainer and leader of the SQL User Group in Adelaide, Australia. Rob is a former director of PASS, and provides consulting and training courses around the world in SQL Server and BI topics.

The power of T-SQL's APPLY operator

Hi! - Great that you've found this page, but it's no longer here! You can find the content over at: http://blogs.lobsterpot.com.au/2011/04/13/the-power-of-t-sqls-apply-operator/

Published Wednesday, April 13, 2011 11:30 AM by Rob Farley

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Paul Bell said:

Excellent article, helped me simplify an overly complex view.

April 13, 2011 10:27 PM
 

Julie Smith said:

I love my t-sql advice peppered with humor ;)

April 14, 2011 9:12 AM
 

Pankush said:

Great article, i am already looking at improving a lot of my complex queries. thanks Rob

April 14, 2011 8:46 PM
 

Matt Velic said:

Rob, seriously, this is awesome stuff. Thanks for the two-fer!

April 18, 2011 10:50 AM
 

jamiet said:

"one day, I’m sure it might."

Talk about hedging your bets :)

July 19, 2011 7:34 AM
 

Rob Farley said:

Well, I want to feel like I can have a faint glimmer of semi-justified hope. ;)

July 19, 2011 7:41 AM
 

Abraham said:

Its cool, could you try to explain more about CTE's later. I'd love to read that.

July 31, 2012 12:42 PM
 

Dan said:

Thanks - helpful article. Also very interesting about the BEGIN and END of a function hurting the optimization.  I hadn't heard of that before

November 21, 2012 11:24 AM
 

bran said:

I used apply but some how it is taking too long. please help

Select shipment_CaseId, shipment_code, P.SiteId, P.[Temp Container Removal]

into #shipment1

From RoseA.dbo.shipment1

outer apply dbo.fn_ShRo(shipment1.shipment_CaseId) [P]

where [P].ClientAbbreviation = (Select top 1 abbreviation from client where client = @iClient)

and [P].Datebil = @dDatebill

December 6, 2012 6:25 PM
 

Rob Farley said:

Bran: that's really going to depend on what dbo.fn_ShRo is. Also though, if the client column in the client table isn't unique, which abbreviation are you getting? The plan would tell me a lot here, of course.

December 6, 2012 6:42 PM
 

Chandu said:

Great Article Rob

March 15, 2013 4:57 PM
 

Wayne said:

Rob

i have a 5 table join where the driving table has foreign keys to these child tables. using a join the section of code is 66% of the total procedure cost. when i change all the joins to cross apply with correlated subqueries, the cost goes down to 7%. i have searched the net for an explanation for this and can't find much. i feel there are probably hidden dangers which is why i am reluctant to implement this into a production environment before i know about them. your thoughts on this?

April 25, 2013 11:48 AM
 

Rob Farley said:

Obviously it's impossible for me to reassure you completely without seeing the queries. However, I suspect you have nothing to worry about. Joins can be performed a number of different ways, and although CROSS APPLY will tend to persuade the database engine to prefer one in particular, if the logic of the query hasn't changed, your results should remain correct.

April 25, 2013 6:02 PM
 

Dave said:

Bitly link not working :(

I was looking forward to the dulcet tones

March 9, 2014 9:22 PM
 

Rob Farley said:

Odd. Works for me, sends me to http://sqlbits.com/Sessions/Event5/Designing_for_simplification (which is just harder to remember).

March 10, 2014 1:10 AM
 

Dave said:

...and it works fine doh. Poor internet today

March 10, 2014 1:51 AM
 

Andrew Watson said:

One minor comment.. While top(3) works... I would humbly suggest that it won't cope very well if the Business requirement, is "return the top 3 performers, by sales amount"... if the amounts are ever tied....

eg:if the product sums are:

One:1000

Two: 800

Three: 500

Four: 500

Would the business want to return the top 4 in this case (as there is a tie for third place), or have a random selection of Three/Four?.

If they would want to see both, then the With query with a RANK (rather than Row_number) function is the more appropriate choice.... definitely more adjustable to business rules  (rank = top performers by amount, dense_rank = top amounts and products with those amounts, row_number = top three by amount [and some other ordering criteria])... don't fight it.  :)

June 5, 2014 9:54 PM
 

Rob Farley said:

You can always use TOP (3) WITH TIES...

June 5, 2014 10:13 PM
 

Valeriy Khvalabov said:

Thank you Rob,

A great article!

You made the explanation simple and straight-forward.

June 13, 2014 10:46 AM
 

Jean Joseph said:

Cool article

October 15, 2014 8:04 PM
 

Johnny said:

Great article.

October 2, 2015 6:25 PM
 

Jodie said:

I know I'm a few years late, but just wanted to say thanks for such a well-explained article and the highly useful examples. I will definitely keep Apply in mind for future SQL conundrums! Thank you :-)

June 28, 2016 11:16 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Tags

No tags have been created or used yet.

News

News? Haven't you read my blog?

My Company


Can't find something?

Contact Me

IM: rob_farley@hotmail.com
Twitter: @rob_farley
Skype: rob_farley
E: rob_farley@hotmail.com

MVP (SQL Server)




Certifications








Adelaide SQL UG

Privacy Statement