1 / 13

Boost your T-SQL with the APPLY Operator

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

chimalsi-mamadou
Télécharger la présentation

Boost your T-SQL with the APPLY Operator

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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;

More Related