Boost your T-SQL with the APPLY Operator

1. Boost your T-SQL with the APPLY Operator Itzik Ben-Gan | T-SQL Trainer and Co-founder, SolidQ Umachandar Jayachandran (UC)| Program Manager, Microsoft

2. Meet Itzik Ben-Gan | ‏@ItzikBenGan • T-SQL Trainer and Co-founder, SolidQ • 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

3. 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

4. Course Topics

5. 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!

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

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

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

9. Joins vs. APPLY X • Cross Join • Both input sets predefined (pre-operator treatment),Result: 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 correlationsResult: unified results of evaluations All Combinations X + ? Matching Combinations 1 2 UnifiedResults

10. CROSS APPLY • Apply right set per left row using correlations • Left rows discarded if right side is empty SELECTC.custid,C.companyname,O.orderid,O.orderdate,O.empid FROMSales.CustomersAS C CROSSAPPLYdbo.GetTopOrders(C.custid, 3)AS O;

11. OUTER APPLY • Apply right set per left row using correlations • Left rows preserved when right side is emptyNULLs used as placeholders SELECTC.custid,C.companyname,O.orderid,O.orderdate,O.empid FROMSales.CustomersAS C OUTERAPPLYdbo.GetTopOrders(C.custid, 3)AS O;

12. Implicit APPLY • Refer to a table UDF in subqueryPass columns from outer table as inputs (correlations) SELECTC.custid,C.companyname, (SELECTCOUNT(DISTINCTempid) FROMdbo.GetTopOrders(C.custid, 3)AS O)ASnumemps FROMSales.CustomersAS C;