With the 2005 and 2012 releases of SQL Server, Microsoft gave us several new functions with amazing capabilities. These functions are part of the ANSI standards, and have nothing to do with the Windows OS. Some of these functions are well known, for example ROW_NUMBER is used by many people. In addition to ROW_NUMBER, we now have the ability to calculate summaries without changing a query to an aggregate query, calculate running totals, pull values from other rows, and perform statistical calculations. This course covers all of the available window functions including syntax, real world examples, and performance considerations.
What is T-SQL?
T-SQL (Transact-SQL) is a programming extenstion to SQL that adds several other features for interacting with relational databases, such as exception and error handling, transaction control, row processing, and much more.
What are T-SQL window functions?
Window functions perform calculations over a set of rows and return a value for each row based on the query. A "window" is simply a set of rows that the function operates on, and the window function uses the values in the window to determine the returned value.
What will I learn in this course?
In this course you will learn how to use T-SQL window functions to solve queries. Some of the topics covered include:
An introduction to window functions
Tools to measure performance
Understanding the OVER clause
Using ranking functions
Window aggregate functions
Accumulating window aggregates
FIRST_VALUE and LAST_VALUE
Who is this course for?
This course is designed for anyone who wants to learn T-SQL window functions in order to more effectively solve complex database queries.
Are there prerequisites to this course?
This is an intermediate level course, so it does assume a fundamental understanding of SQL and T-SQL. But as far as window functions themselves, no prior experience is expected.
Kathi Kellenberger is a SQL Server MVP and a Teammate with Linchpin People. She is the author or co-author of several SQL Server books, including Beginning T-SQL from Apress, and frequently speaks at community events such as PASS Summit and SQL Saturday.