APPLY - 1

Question

APPLY - 1

Ron McCullough

SSC Guru

Points: 63877
More actions
September 5, 2012 at 8:12 pm

#281221

Comments posted to this topic are about the item APPLY - 1
If everything seems to be going well, you have obviously overlooked something.
Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]
September 6, 2012 at 4:43 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 43 total)

You must be logged in to reply to this topic. Login to reply

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 1

Good, solid question, Ron. And on a subject that probably can use a bit more propaganda; I think many SQL Server people still don't really know what APPLY is and how it works. And it's good to see the "1" in the question name; I have a feeling that I already know what I can expect in the follow-up question.

My only (minor) gripe is the order in which the answer options are presented. Why not simply order them (lowest to highest or highest to lowest) instead of this random order?

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 2

Thanks for this question on the basics Ron.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Raghavendra Mudugal SSChampion Points: 10658 More actions · Answer 3

Thank you, Bitbucket. This question is very nice.

Initially I picked 6 and then changed to 5, thinking of the S in A has 6 unique value and then R in A has only 5 unique values and was expecting it will work like LEFT JOIN and was wrong.

Also I was expecting the data will be dirty, but seeing the dataset each row has its unique representation with the combination of A-R and B-R.

SRS

AlphaD12341001

AlphaD12342001

betaA11223001

betaA11224001

bravoC13425001

charlieD12341001

charlieD12342001

harryA11223001

harryA11224001

ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

Erav SSC-Addicted Points: 411 More actions · Answer 4

Erav

SSC-Addicted

Points: 411

September 6, 2012 at 1:05 am

#1534261

very good question. Thanks.

-Erav

Arthur Olcot SSCertifiable Points: 6639 More actions · Answer 5

Great question!... i kept counting 10 records until I realised that "Doug" would actually be eliminated from the apply.

Thanks.

Christian Buettner-167247 SSChampion Points: 13729 More actions · Answer 6

Good question, but the answer from MS is not really applicable, since we have a sub-query expression and not a TVF in the QTOD example.

The more verbose explanation is documented with the FROM clause:

http://msdn.microsoft.com/en-us/library/ms177634%28v=sql.105%29.aspx

Best Regards,

Chris Büttner

TomThomson SSC Guru Points: 104773 More actions · Answer 7

Good question.

Too early in the morning for me, apparently my brain thinks that 2+2+2+1+0+2 = 8 before breakfast.

Tom

Skanker Hall of Fame Points: 3059 More actions · Answer 8

Thanks for this question - I really enjoyed trying to work it out in my head. That I then got it right was an added bonus to create a good start to the day.

Being able to copy and paste the the query into management studio is a good way to follow up on this learning - so thanks for the change in policy on QoD's. I never had time to type in some of the queries that were submitted previously so quite possibly did not get the most from them.

Rob Schripsema SSCertifiable Points: 7469 More actions · Answer 9

Great question! A bit mind numbing at ...let's see...it's 1:30am here ...:doze: ... but a good refresher on CROSS APPLY. Thanks!

Rob Schripsema
Propack, Inc.

(Bob Brown) SSCrazy Points: 2705 More actions · Answer 10

Thanks for the great question. I did good for 6:00 AM but have to research the CROSS APPLY more deeply.

derek.colley SSCrazy Eights Points: 8040 More actions · Answer 11

Good question, I had it in my head that CROSS APPLY acted like an INNER JOIN and OUTER APPLY acted like an OUTER JOIN, but in fact the CROSS apply would return unique combinations, excluding NULLs on the right - hence my answer of 5.

Never mind, another QOTW chance tomorrow...

---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

(Bob Brown) SSCrazy Points: 2705 More actions · Answer 12

derek.colley (9/6/2012)

Derek, Tried the CAST statement in you Post Signature and got an error. Is it supposed to be valid SQL? Thanks.

ronmoses@gmail.com SSCarpal Tunnel Points: 4480 More actions · Answer 13

This is the first QotD in a long time (ever?) that, after getting it right, I audibly said "WOO-HOO!"... at work, in an open office area. So yeah, a few strange looks.

Thanks for the great question, I can't wait to get part 2 wrong! 😀

Ron (a different one)

-----
a haiku...

NULL is not zero
NULL is not an empty string
NULL is the unknown