1 / 15

T-SQL Window function deep dive part 2

T-SQL Window function deep dive part 2. Kathi Kellenberger @ auntkathi kathi.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

sdavid
Download Presentation

T-SQL Window function deep dive part 2

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. T-SQL Window function deep dive part 2 Kathi Kellenberger @auntkathi kathi.Kellenberger@red-gate.com http://auntkathisql.com

  2. 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 queries easier to write • Often better performance What are window functions?

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

  4. Execution Plan Operators

  5. Execution Plan Operators

  6. Execution Plan Operators

  7. POC Index (from Itzik Ben-Gan) • Filtered column(s) + Partition column(s) + Order by column(s) + Covering columns(s) Indexes

  8. Use caution with window aggregates • Pre-aggregate if possible • Older techniques might perform better Window Aggregates

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

  10. Performance

  11. Performance

  12. POC index can help all window functions • Use ROWS for framing • Use window aggregates (without ORDER BY) with caution Summary

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

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

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

More Related