Unlocking the Power of T-SQL Dynamic Duo: Window Functions and CTEs
Dive into the world of T-SQL Dynamic Duo with a focus on Window Functions and Common Table Expressions (CTEs). Learn about the anatomy of Window Functions, different types of functions like aggregate and offset functions, and how CTEs can enhance your SQL queries. Discover the real dynamic duo of Window Functions and CTEs and how they can elevate your data analysis skills. Get insights into ranking, framing concepts, running totals, and more, all from a session overview enriched with valuable content from Itzik Ben-Gan's book. Explore the possibilities of utilizing Aggregate Functions, including SUM, COUNT, MIN, MAX, and AVG, alongside Framing and Offset Functions like LAG, LEAD, FIRST_VALUE, LAST_VALUE, and NTH_VALUE. Experience a demo to see these concepts in action and unlock the potential of T-SQL Dynamic Duo for your data analysis tasks.
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
You are allowed to download the files provided on this website for personal or commercial use, subject to the condition that they are used lawfully. All files are the property of their respective owners.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.
E N D
Presentation Transcript
T-SQL DYNAMIC DUO: WINDOW FUNCTIONS AND C0MMON TABLE EXPRESSIONS (CTE)
ABOUT ME -Started my technical journey at this local SQL Saturday -Microsoft Certified Professional -Microsoft Technology Associate: Database Fundamentals Day Job: -Data Analyst with MoneySolvers in Jacksonville, FL Blog at: http://susansblog.sqlinsight.net/ How to Become Technical at Any Age -email: susanh@sqlinsight.net -Certified Lean Six Sigma Yellow Belt -Weekend Sailor and Wanna-Be Power BI Guru -Motto One: Learn as much as you, as fast as you can for as long as you can. -Motto Two: Be the best at what you do and learn more to be better!
SESSION OVERVIEW Window Functions and CTEs Explained Anatomy of a Window Function Ranking Functions Aggregate Functions and Percentages Offset Functions Framing Concepts and Running Totals Median and More Q & A as time allows Information for this presentation taken primarily from Itzik Ben-Gan s book: Microsoft SQL Server 2012 High Performance T-SQL Using Window Functions.
MEET THE REAL DYNAMIC DUO Window Functions Nothing to do with Windows operating system Window Function performs a calculation over a set of rows The Window is defined by the OVER clause The OVER clause may contain a PARTITION BY option , Order BY option and/or Framing options. CTE (Common Table Expressions) Creates a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement Begin a CTE with a WITH Clause and expression name: With CTE_C as (Select ..) If more than one CTE, you must separate them with commas. Great for troubleshooting code
Aggregate Functions SUM() COUNT() MIN() MAX() AVG() Offset Functions Framing LAG LEAD FIRST_VALUE LAST_VALUE NTH VALUE- Not implemented with SQL Server 2012 Framing is another filter that further restricts the rows in a partition.
Anatomy of a Window Function Function ( ) OVER Clause (Core Elements: Partitioning .Ordering Framing) Partition By option: Restricts the rows into smaller sets. Order By option: defines the ordering for the calculation Framing option: Further restricts the rows in the partition and used in Aggregation and Offset functions
Window Function Recap Partitioning -Suggest Filtering rather than Grouping -Supported by ALL Window Functions -If Partition By Clause not used, the window is NOT restricted [meaning the default partition is to consider the entire result set as one partition. Ordering -Defines the ordering for the Calculation, not presentational ordering -Framing - An additional option to further restrict the rows in the window partition -Window Functions lend themselves to better optimization than subqueries. -Not all Window Functions support all elements -No major changes to Window Functions since release of SQL Server 2012, but 2016 has Batchmode Window Function performance improvements.
Resources - Microsoft SQL Server 2012 High_Performance T-SQL Using Window Functions by Itzik Ben-Gan [Great Book!]: Search on Amazon Video by Itzik Ben-Gan https://sqlbits.com/Ses sions/Event17/Window _Functions -Intro article to Window Functions: https://www.red-gate.com/simple-talk/sql/t-sql-programming/introduction- to-t-sql-window-functions/ White Papers http://tsql.solidq.com/r esources/ -SQL Window Functions Introdution: https://drill.apache.org/docs/sql-window-functions-introduction/ -CTE s: https://docs.microsoft.com/en-us/sql/t-sql/queries/with-common- table-expression-transact-sql?view=sql-server-2017 -OVER clause: https://docs.microsoft.com/en-us/sql/t-sql/queries/select- over-clause-transact-sql?view=sql-server-2017 SQL 2019 Functionality: https://www.red-gate.com/simple-talk/sql/t-sql- programming/the-performance-of-window-aggregates-revisited-with-sql- server- 2019/?utm_source=simpletalk&utm_medium=pubemail&utm_content=201 90226-slota1&utm_term=simpletalkmain
QUESTIONS Contact Info: susanh@SQLInsight.net Blog: http://susansblog.sqlinsight.net/ LinkedIn: https://www.linkedin.com/in/susan- schneider-01946019/
THE END OF THE PRESENTATION THE BEGINNING OF A LOVE FOR WINDOW FUNCTIONS AND CTES Join me for the next session: Women In Technology I am on the panel
