TSQL WINDOW FUNCTION DEEP DIVE PART 2 Kathi

Nothing to do with Windows OS Standard functionality added to T-SQL Functions that

2005 ROW_NUMBER(), RANK(), DENSE_RANK() and NTILE() Window aggregates 2012 Framing Enhanced window aggregates

POC Index (from Itzik Ben-Gan) Filtered column(s) + Partition column(s) + Order by

Use caution with window aggregates Pre-aggregate if possible Older techniques might perform better

Default frame: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW Better performance with ROWS

Subtotals – 30 million rows 2 1. 8 1. 75 1. 6 1. 4

Running Totals 60 50. 5 50 7. 5 M 15 M 30 M 40

POC index can help all window functions Use ROWS for framing Use window

Adam Machanic’s Big Adventure Script http: //sqlblog. com/blogs/adam_machanic/archive/2011/10/17/thinking-bigadventure. aspx My book: Expert T-SQL

Just like Jimi Hendrix … We love to get feedback Please complete the session

SQLBits - It's all about the community. . . Please visit Community Corner, we

Slides: 15

Download presentation

T-SQL WINDOW FUNCTION DEEP DIVE PART 2 Kathi Kellenberger @auntkathi. Kellenberger@red-gate. com http: //auntkathisql. com

Nothing to do with Windows OS Standard functionality added to T-SQL Functions that operate on a set or window of rows Always with an OVER clause (but sometimes you will see an OVER clause without a window function) Always found in the SELECT and ORDER BY Makes Often queries easier to write better performance WHAT ARE WINDOW FUNCTIONS?

2005 ROW_NUMBER(), RANK(), DENSE_RANK() and NTILE() Window aggregates 2012 Framing Enhanced window aggregates with ORDER BY Analytic functions Offset functions WHAT ARE WINDOW FUNCTIONS?

EXECUTION PLAN OPERATORS

POC Index (from Itzik Ben-Gan) Filtered column(s) + Partition column(s) + Order by column(s) + Covering columns(s) INDEXES

Use caution with window aggregates Pre-aggregate if possible Older techniques might perform better WINDOW AGGREGATES

Default frame: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW Better performance with ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW IMPORTANT: If ORDER BY column values are not unique, will get different results using RANGE FRAMING

Subtotals – 30 million rows 2 1. 8 1. 75 1. 6 1. 4 1. 2 1 1 0. 75 0. 8 0. 5 0. 6 0. 5 0. 4 0. 2 0 Window Aggregates CTE 1 Calc PERFORMANCE 2 Calc Correlated Subquery 3 Calc

Running Totals 60 50. 5 50 7. 5 M 15 M 30 M 40 30 17 20 10 21. 5 2 4 11 8 0 WF Default Frame 0 0. 5 1 WF Rows Frame PERFORMANCE 6 4 Join/SQ Cursor

POC index can help all window functions Use ROWS for framing Use window aggregates (without ORDER BY) with caution SUMMARY

Adam Machanic’s Big Adventure Script http: //sqlblog. com/blogs/adam_machanic/archive/2011/10/17/thinking-bigadventure. aspx My book: Expert T-SQL Window Functions Itzik Ben-Gan’s book: Microsoft SQL Server 2012 High-Performance TSQL Using Window Functions http: //auntkathisql. com Beginning T-SQL 3 rd Edition by Kathi Kellenberger and Scott Shaw RESOURCES

Just like Jimi Hendrix … We love to get feedback Please complete the session feedback forms

SQLBits - It's all about the community. . . Please visit Community Corner, we are trying this year to get more people to learn about the SQL Community, equally if you would be happy to visit the community corner we’d really appreciate it.