T-SQL Power! Windows That Open Doors Adam

Presentation on theme: "T-SQL Power! Windows That Open Doors Adam"— Presentation transcript:

1 T-SQL Power! Windows That Open Doors Adam Machanic @AdamMachanic

2 T-SQL POWER! © 2016 Adam Machanic Adam Machanic Consultant Boston, MA and Beyond Speaker PASS, TechEd, Connections … Writer Books, magazines, web sites Founder SQLBlog.com amachanic@gmail.com @AdamMachanic

3 T-SQL POWER! © 2016 Adam Machanic Agenda Background Lots of Demos(That’s it.)

4 T-SQL POWER! © 2016 Adam Machanic Windowing and Framing … 1010110110101010101110010100100010101001010010010010000101010 … Frames – as many as you need

5 T-SQL POWER! © 2016 Adam Machanic Framing Rows ROWS BETWEEN 1 PRECEDING AND CURRENT ROW ROWS BETWEEN 3 PRECEDING AND 2 FOLLOWING

6 T-SQL POWER! © 2016 Adam Machanic Framing Rows ROWS BETWEEN 1 PRECEDING AND CURRENT ROW ROWS BETWEEN 3 PRECEDING AND 2 FOLLOWING

7 T-SQL POWER! © 2016 Adam Machanic Framing Rows ROWS BETWEEN 1 PRECEDING AND CURRENT ROW ROWS BETWEEN 3 PRECEDING AND 2 FOLLOWING

8 T-SQL POWER! © 2016 Adam Machanic Framing Rows ROWS BETWEEN 1 PRECEDING AND CURRENT ROW ROWS BETWEEN 3 PRECEDING AND 2 FOLLOWING

9 T-SQL POWER! © 2016 Adam Machanic Framing Rows ROWS BETWEEN 1 PRECEDING AND CURRENT ROW ROWS BETWEEN 3 PRECEDING AND 2 FOLLOWING

10 T-SQL POWER! © 2016 Adam Machanic Framing Rows ROWS BETWEEN 1 PRECEDING AND CURRENT ROW ROWS BETWEEN 3 PRECEDING AND 2 FOLLOWING

11 T-SQL POWER! © 2016 Adam Machanic Framing Rows ROWS BETWEEN 1 PRECEDING AND CURRENT ROW ROWS BETWEEN 3 PRECEDING AND 2 FOLLOWING

12 T-SQL POWER! © 2016 Adam Machanic Framing Rows ROWS BETWEEN 1 PRECEDING AND CURRENT ROW ROWS BETWEEN 3 PRECEDING AND 2 FOLLOWING

13 T-SQL POWER! © 2016 Adam Machanic Partitioning and Framing Rows ROWS BETWEEN 1 PRECEDING AND CURRENT ROW ROWS BETWEEN 3 PRECEDING AND 2 FOLLOWING

14 T-SQL POWER! © 2016 Adam Machanic Partitioning and Framing Rows ROWS BETWEEN 1 PRECEDING AND CURRENT ROW ROWS BETWEEN 3 PRECEDING AND 2 FOLLOWING

15 T-SQL POWER! © 2016 Adam Machanic Partitioning and Framing Rows ROWS BETWEEN 1 PRECEDING AND CURRENT ROW ROWS BETWEEN 3 PRECEDING AND 2 FOLLOWING

16 T-SQL POWER! © 2016 Adam Machanic Partitioning and Framing Rows ROWS BETWEEN 1 PRECEDING AND CURRENT ROW ROWS BETWEEN 3 PRECEDING AND 2 FOLLOWING

17 T-SQL POWER! © 2016 Adam Machanic Partitioning and Framing Rows ROWS BETWEEN 1 PRECEDING AND CURRENT ROW ROWS BETWEEN 3 PRECEDING AND 2 FOLLOWING

18 T-SQL POWER! © 2016 Adam Machanic Partitioning and Framing Rows ROWS BETWEEN 1 PRECEDING AND CURRENT ROW ROWS BETWEEN 3 PRECEDING AND 2 FOLLOWING

19 T-SQL POWER! © 2016 Adam Machanic Partitioning and Framing Rows ROWS BETWEEN 1 PRECEDING AND CURRENT ROW ROWS BETWEEN 3 PRECEDING AND 2 FOLLOWING

20 T-SQL POWER! © 2016 Adam Machanic Partitioning and Framing Rows ROWS BETWEEN 1 PRECEDING AND CURRENT ROW ROWS BETWEEN 3 PRECEDING AND 2 FOLLOWING

21 T-SQL POWER! © 2016 Adam Machanic Partitioning and Framing Rows ROWS BETWEEN 1 PRECEDING AND CURRENT ROW ROWS BETWEEN 3 PRECEDING AND 2 FOLLOWING

22 T-SQL POWER! © 2016 Adam Machanic Commonly Used Window Function Types Ranking ROW_NUMBER RANK DENSE_RANK Analytic Offset LAG LEAD Aggregate SUM AVG COUNT MIN MAX Analytic Framed FIRST_VALUE LAST_VALUE

23 T-SQL POWER! © 2016 Adam Machanic Query Processor Iterators of Interest

24 T-SQL POWER! WINDOWS THAT OPEN DOORS demo

25 T-SQL POWER! © 2016 Adam Machanic Summary Window functions make complex T-SQL much easier Performance, on the other hand, is up to you Be creative! There are endless use cases for these features Beware of RANGE mode