April 12, 2010 at 8:04 am
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
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 12, 2010 at 8:30 am
this article gave me an idea about the problem I'm working on....Most of all, I like the way the article is presented.
April 12, 2010 at 8:39 am
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.
April 12, 2010 at 8:42 am
Good stuff. Looking forward to part 2.
April 12, 2010 at 8:59 am
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
April 12, 2010 at 9:37 am
April 12, 2010 at 9:46 am
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.
April 12, 2010 at 9:54 am
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
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
April 12, 2010 at 9:55 am
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
April 12, 2010 at 12:11 pm
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
Change is inevitable... Change for the better is not.
April 12, 2010 at 12:34 pm
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
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.
April 12, 2010 at 1:48 pm
Really nice article.
April 12, 2010 at 4:36 pm
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
April 12, 2010 at 4:42 pm
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
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 12, 2010 at 4:46 pm
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
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 15 posts - 16 through 30 (of 89 total)
You must be logged in to reply to this topic. Login to reply