Understanding and Using APPLY (Part 1)

Question

Understanding and Using APPLY (Part 1)

Viewing 15 posts - 16 through 30 (of 89 total)

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

Paul White SSC Guru Points: 150468 More actions · Answer 1

Arjun Sivadasan (4/12/2010)
...They all showed me the same execution plan. Am I not 'seeing' something?

Hey Arjun, no you are not missing anything - but I do cover all these questions in Part 2 in some detail. These are all great questions, but please bear with me until next week's concluding part.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

RJ-476420 SSCarpal Tunnel Points: 4806 More actions · Answer 2

this article gave me an idea about the problem I'm working on....Most of all, I like the way the article is presented.

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 3

I will join the praise of the article. Very thought provoking. I am looking forward to Part II and I am wondering if I am going to have to rethink my standard logic approach to many problems. Challenging standard thought processes and finding new and improved ways to tackle problems is one of the primary benefits to SSC.

Thanks.

Bill Kline-270970 SSC Eights! Points: 915 More actions · Answer 4

Bill Kline-270970

SSC Eights!

Points: 915

April 12, 2010 at 8:42 am

#1150093

Good stuff. Looking forward to part 2.

WayneS SSC Guru Points: 95460 More actions · Answer 5

BTW Paul, I really like the way you did the data diagram with grids under the tables (so much that I'll probably utilize that technique in any future articles that I might write :-D).

I see that the function utilizes "WITH SCHEMABINDING". Is this a necessary step?

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Richard M. SSCertifiable Points: 7401 More actions · Answer 6

Great article Paul! Looking forward to next weeks article.

_______________________________________________________________________
For better assistance in answering your questions, click here[/url]

JJ B SSCertifiable Points: 5130 More actions · Answer 7

This is the best article on APPLY that I've read. I particularly appreciate the paragraph: "APPLY calls the function once for each row from the input. ...to produce the eventual full result." It is very well worded and clear.

Usually I adopt new T-SQL offerings in each version right away, making great use of them. For example, I started using Output and FOR XML the second they were available to me. But no matter how many articles I have read on APPLY, I have yet to use it. Perhaps with your articles I will at least see why people like them and perhaps find a use for it myself.

Thanks for taking the time to do this. This is one of those areas for me that is a "hole". It is a beginning concept, but I just haven't gotten it yet.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

Paul White NZ (4/10/2010)
I would like to express my sincere thanks to the following people, for their help in producing this article:
Amanda Lawrence
Jeff Moden MVP
Chris Morris
Lutz Müller
Jason Brimhall[/url]
Thank you everyone!
Paul

You are quite welcome. It was a pleasure.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQLRNNR SSC Guru Points: 281334 More actions · Answer 9

Great article Paul.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 10

Nicely done, Paul. The impeccably timed/highly appropriate graphics, the simple code examples, and the straight forward "talk" make this a model for future articles. It was a pleasure to read and, judging from the discussions so far, it looks like it really hit the mark for understanding. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

LutzM SSC Guru Points: 107049 More actions · Answer 11

CirquedeSQLeil (4/12/2010)
Paul White NZ (4/10/2010)
I would like to express my sincere thanks to the following people, for their help in producing this article:
Amanda Lawrence
Jeff Moden MVP
Chris Morris
Lutz Müller
Jason Brimhall[/url]
Thank you everyone!
Paul
You are quite welcome. It was a pleasure.

The same from my side! Additionally, I'd like to thank you for giving me the opportunity to participate in the "evolution" of that very fine article. I learned a lot.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Glen Cooper SSCommitted Points: 1927 More actions · Answer 12

Glen Cooper

SSCommitted

Points: 1927

April 12, 2010 at 1:48 pm

#1150311

Really nice article.

Trey Staker SSCarpal Tunnel Points: 4796 More actions · Answer 13

Thanks Paul! Very well organized and written. Plus I learned something new. 🙂 I'm looking forward to Part 2.

---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum

Paul White SSC Guru Points: 150468 More actions · Answer 14

WayneS (4/12/2010)
BTW Paul, I really like the way you did the data diagram with grids under the tables (so much that I'll probably utilize that technique in any future articles that I might write :-D).

Glad you found it helpful - I would be flattered to see you use the idea in your next article.

I see that the function utilizes "WITH SCHEMABINDING". Is this a necessary step?

There are some notes on this in the attached scripts - but essentially, no it is not required as such - but I do recommend it.

In general, I always schema-bind functions, unless there is a very good reason not to. Not only does it prevent the schema of referenced entities changing unexpectedly, it can also have significant optimization benefits.

If a function (scalar, iTVF, or multi-statement) is not marked as schema-bound, SQL Server marks it as non-deterministic, as performing user data access, and as performing system data access. This is a performance optimization - to avoid having to check the function and all dependent objects for these properties on every call.

This can have important performance consequences, since the Query Optimizer has many fewer plan options when it encounters a non-deterministic element. Marking a function as schema-bound forces the engine to statically check the function in detail to determine if it is deterministic or not.

For function that do not do data access, being marked as doing so can hurt UPDATE performance in particular - see http://blogs.msdn.com/sqlprogrammability/archive/2006/05/12/596424.aspx.

You can check the properties of a function using the OBJECTPROPERTYEX built-in function:

SELECT OBJECTPROPERTYEX(OBJECT_ID(N'dbo.GetSubjectsAndScores', N'IF'), N'IsDeterministic');

SELECT OBJECTPROPERTYEX(OBJECT_ID(N'dbo.GetSubjectsAndScores', N'IF'), N'UserDataAccess');

SELECT OBJECTPROPERTYEX(OBJECT_ID(N'dbo.GetSubjectsAndScores', N'IF'), N'SystemDataAccess');

Paul

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Paul White SSC Guru Points: 150468 More actions · Answer 15

dbowlin (4/12/2010)
I will join the praise of the article. Very thought provoking. I am looking forward to Part II and I am wondering if I am going to have to rethink my standard logic approach to many problems. Challenging standard thought processes and finding new and improved ways to tackle problems is one of the primary benefits to SSC.

This comment pleases me immensely. One of the primary aims of the article is to get people thinking about how APPLY can be used to approach some types of problems from a new angle.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi