[blog] T-SQL Tuesday #17 – APPLY Knowledge #tsql2sday


APPLY Knowledge ™ 😉
The APPLY operator allows you to run T-SQL for each row of an outer data set.
See also:

 

Image004

Multi-Statement Table-Valued User-Defined Functions (UDFs)
vs.
Inline Statements (Derived Table JOIN, SELECT Correlated Subquery, etc.)

  • One reason you might consider placing statements inside of a UDF instead of as inline statements is Readability.  Let’s take a look at a UDF in AdventureWorks2008R2.  You have a copy of that database handy right?  Imagine how much more complex looking the below query would be if we pulled the statements out of the UDF and added them inline.

    SELECT

           [GetContactInformation].* /* <shutter> */

          ,[ModifiedDate]

    FROM [Person].[BusinessEntityContact]

    CROSS APPLY [dbo].[ufnGetContactInformation] ([Person].[BusinessEntityContact].[PersonID]) AS [GetContactInformation];

  • Another reason you might consider UDF encapsulation instead of inline statements is Modularity.  This helps you avoid frequently copying and pasting a set of statements.   If that set of statements needed changes in logic or object names then only a single update of the UDF would be needed.
  • There are many statements valid within a UDF which are not valid as inline statements.  An overgeneralization of this Interoperability would be to consider only SELECT statements as valid for inline statements but any statement without a side effect as valid for a UDF.
  • Schema-binding is possible with a UDF.  This can be beneficial when you want to find dependencies of reused statements.  Before making a UDF change you can locate all schema-bound objects to assess the validity and the impact of a change.
  • Common runtime language (CLR) coding is possible with a UDF.  This provides you with another entire language to use beyond T-SQL statements.
  • Security context switching is possible with a UDF.  You can include the EXECUTE AS statement within a UDF to force execution of the UDF to be performed under a different security context.
  • XML data type usage is possible with a UDF.  This allows you to nest a dataset into each row of the outer data set.
  • A single UDF can return multiple columns or multiple rows per call  but only one column and one row can be returned per correlated subquery call.
  • Although both UDFs and inline statements can have Hints applied from the outer query, in the future they will not applied to UDFs.
  • Unlike inline statements, the APPLY operator requires the database to be in Compatibility Level 90 or higher.

 

Image006

The Champion
Repeat after me, “It depends”.  My guess is most people will end up using APPLY only after trying to use more familiar operators first.  To keep your edge, use the above considerations to make better, faster decisions.  To keep your eye of the tiger, listen to Survivor.

Advertisements

About Robert Matthew Cook

Hello, my name is Robert Matthew Cook. This blog is autoposted to from my main blog at www.sqlmashup.com. For more profile information or to leave a comment, please visit me there.
This entry was posted in Uncategorized. Bookmark the permalink.