Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
Failover Clustering in the Cloud - Understanding Your Options - Free Webinar
 

Introduction to SQL Server Window Functions using T-SQL



By:
Overview

Window functions are a specific subset of functions in T-SQL – the dialect of the SQL language Microsoft uses in their database product Microsoft SQL Server – allowing you to create more complex queries. Their application ranges from statistical queries, calculating subtotals, helping you identify duplicate rows, to solving well known problems such as the gaps and islands problem.

With window functions, you can solve some problems in T-SQL more elegantly than before. In a few cases, it allows you to write a set-based query to solve your issue, instead of resorting to a cursor, which is in most cases a slower alternative.

Outline

In this tutorial, we’ll give you an introduction to the concepts and usage of the window functions. Learning about these functions and applying them in your everyday programming will make you write better and more efficient T-SQL.

After some introduction work, we’ll go over some of the different categories of window functions. The high-level outline is as follows:

  • Some examples with a couple of use cases
  • Introduction to window functions
  • Aggregate functions
  • The ROW_NUMBER function
  • Ranking functions
  • Get access to other rows with LAG and LEAD and other offset functions
  • Statistical functions
  • Improving performance
  • The Gaps and Islands problem

You can use the outline on the left to browse the tutorial or use the arrows “Previous” and “Next”.

Additional Information

Last Update: 5/31/2018




More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Wednesday, July 25, 2018 - 6:16:50 AM - Jonathan Mukundu Back To Top

Exellent article! 


Learn more about SQL Server tools