Itzik BenGan TSQL Trainer and Cofounder Solid Q

Itzik Ben-Gan | T-SQL Trainer and Co-founder, Solid. Q Umachandar Jayachandran (UC) | Program Manager, Microsoft

Meet Itzik Ben-Gan | @Itzik. Ben. Gan • T-SQL Trainer and Co-founder, Solid. Q – Delivering T-SQL training around the world (http: //tsql. solidq. com/courses. htm) – Author of many T-SQL books – Columnist, SQL Server Pro Magazine – Regular speaker at events like SQL PASS and Dev Connections – Over two decades of experience with SQL and databases

Meet Umachandar Jayachandran (UC) • Program Manager, Microsoft – SQL Server Product Group – Worked on SQL Server since 2008 release – Currently working on Windows Azure SQL Database – Have worked with databases (Sybase, Oracle & SQL Server) since 1998 – Microsoft representative for ANSI SQL standards group

Course Topics Boost your T-SQL with the APPLY Operator 01 | APPLY, Described 02 | APPLY Boosts various T-SQL Features

Setting Expectations • Target Audience – T-SQL practitioners with at least half a year of experience • Suggested Prerequisites/Supporting Material – Microsoft SQL Server 2012 T-SQL Fundamentals (MSPress, 2012) – Source code: http: //tsql. solidq. com/books/source_code/APPLY. txt • Few leading slides – mostly T-SQL coding demos!

Join the MVA Community! • Microsoft Virtual Academy – Free online learning tailored for IT Pros and Developers – Over 1 M registered users – Up-to-date, relevant training on variety of Microsoft products

01 | APPLY, Described Itzik Ben-Gan | T-SQL Trainer and Co-founder, Solid. Q Umachandar Jayachandran (UC) | Program Manager, Microsoft

Module Overview • Joins vs. APPLY • CROSS APPLY • OUTER APPLY • Implicit APPLY

Joins vs. APPLY • Cross Join – Both input sets predefined (pre-operator treatment), Result: all combinations X All Combinations • Inner Join – Both input sets predefined, Result: only matching combinations • APPLY (2005+) – Left input set predefined, Right set evaluated per left row using correlations Result: unified results of evaluations X+? Matching Combinations 1 2 Unified Results

CROSS APPLY • Apply right set per left row using correlations • Left rows discarded if right side is empty SELECT C. custid, C. companyname, O. orderid, O. orderdate, O. empid FROM Sales. Customers AS C CROSS APPLY dbo. Get. Top. Orders(C. custid, 3) AS O;

OUTER APPLY • Apply right set per left row using correlations • Left rows preserved when right side is empty NULLs used as placeholders SELECT C. custid, C. companyname, O. orderid, O. orderdate, O. empid FROM Sales. Customers AS C OUTER APPLY dbo. Get. Top. Orders(C. custid, 3) AS O;

Implicit APPLY • Refer to a table UDF in subquery Pass columns from outer table as inputs (correlations) SELECT C. custid, C. companyname, (SELECT COUNT(DISTINCT empid) FROM dbo. Get. Top. Orders(C. custid, 3) AS O) AS numemps FROM Sales. Customers AS C;

© 2013 Microsoft Corporation. All rights reserved. Microsoft, Windows, Office, Azure, System Center, Dynamics and other product names are or may be registered trademarks and/or trademarks in the U. S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.