1 / 30

T-SQL Power! The OVER Clause: Your Key to No-Sweat Problem Solving

Learn how to use the OVER clause in T-SQL for advanced problem solving. Discover innovative techniques and examples of GROUPING vs. PARTITIONING with practical demos.

piperel
Download Presentation

T-SQL Power! The OVER Clause: Your Key to No-Sweat Problem Solving

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. Required Slide SESSION CODE: DAT317 T-SQL Power! The OVER Clause: Your Key to No-Sweat Problem Solving Adam Machanic Database Consultant SQLblog

  2. WHAT IS POWER?

  3. INNOVATION SHOULD BE EASY!

  4. About Me Independent SQL Server and .NET Consultant Boston, MA AuthorSQL Server 2008 Internals Expert SQL Server 2005 Development Conference and INETA Speaker Connections, PASS, TechEd, DevTeach, etc. Co-Founder: SQLblog.com The SQL Server Blog Spot on the Web amachanic@gmail.com

  5. Grouping vs. Partitioning Grouping is an N1 transformation { 3 rows  1 row { 2 rows  1 row

  6. Grouping vs. Partitioning Grouping is an N1 transformation { 1 row { 1 row We gain insight, but lose information

  7. Grouping vs. Partitioning Partitioning is an NN transformation { 3 rows  3 rows { 2 rows  2 rows

  8. Grouping vs. Partitioning Partitioning is an NN transformation { 3 rows { 2 rows We gain insight, and lose nothing

  9. Introducing the OVER Clause First available in SQL Server 2005 Can be applied to any system or user-defined aggregate for partitioning Can be applied to a new set of “windowed” functions for ordered metrics

  10. Aggregation: The Old Way SELECT ColA, ColB, SUM(Val) FROM T1 GROUP BY ColA, ColB

  11. Aggregation: With OVER SELECT ColA, ColB, SUM(Val) OVER ( PARTITION BY ColA, ColB ) FROM T1

  12. We Aren’t Required to Partition… SELECT ColA, ColB, SUM(Val) OVER() FROM T1

  13. Multiple Partitions in One Query? Sure. SELECT ColA, ColB, SUM(Val) OVER ( PARTITION BY ColA, ColB ) AS PartitionedVal, SUM(Val) OVER() AS NonPartitionedVal FROM T1

  14. Ranking and Numbering Functions • SQL Server 2005/2008 windowing functions: • ROW_NUMBER • RANK • DENSE_RANK • NTILE • These functions can be both partitioned and ordered

  15. ROW_NUMBERUniquely numbers rows

  16. ROW_NUMBER – PartitionedUniquely numbers rows, restarting at each partition

  17. RANKRanks rows, skipping numbers for ties

  18. DENSE_RANKRanks rows, not skipping numbers for ties

  19. NTILE(x)Creates closest-match subgroups based on desired size “x”

  20. Under the Covers • You didn’t think this was all free—right? • Partitioning and ordering both require input rows to be sorted • Indexes can be aligned to reduce or eliminate the need to sort • All of the partitioning work is done with the Segment iterator • Watch out for spools!

  21. AndNow: A Bunch of Demos!

  22. The OVER Clause, In Depth! demo

  23. Summary The OVER clause makes powerful T-SQL easy… …and perhaps even fun Great performance if you do a bit of work upfront Don’t forget to be creative!

  24. Required Slide Track PMs will supply the content for this slide, which will be inserted during the final scrub. DAT Track Scratch 2 Win • Find the DAT Track Surface Table in the Yellow Section of the TLC • Try your luck to win a Zune HD • Simply scratch the game pieces on the DAT Track Surface Table and Match 3 Zune HDs to win

  25. Required Slide Resources Learning • Sessions On-Demand & Community • Microsoft Certification & Training Resources www.microsoft.com/teched www.microsoft.com/learning • Resources for IT Professionals • Resources for Developers • http://microsoft.com/technet • http://microsoft.com/msdn

  26. Required Slide Complete an evaluation on CommNet and enter to win!

  27. Sign up for Tech·Ed 2011 and save $500 starting June 8 – June 31st http://northamerica.msteched.com/registration You can also register at the North America 2011 kiosk located at registrationJoin us in Atlanta next year

  28. © 2010 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista 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.

  29. Required Slide

More Related