Book Image

SQL Server 2014 Development Essentials

By : Basit A. Masood-Al-Farooq
Book Image

SQL Server 2014 Development Essentials

By: Basit A. Masood-Al-Farooq

Overview of this book

Table of Contents (14 chapters)
SQL Server 2014 Development Essentials
About the Author
About the Reviewers

Using the Transact-SQL analytic window functions

SQL Server 2014 supports several analytic functions. With the help of these window analytic functions, we can perform common analyses, such as ranking, percentiles, moving averages, and cumulative sums that can be expressed concisely in a single SELECT statement.

Before the advent of analytic functions, the solution for performing complex analytical tasks was to use self joins, correlated subqueries, temporary tables, or some combination of all three. This solution was inefficient and highly resource intensive. Expressing queries with analytic functions simplifies complex tasks by eliminating programming self joins and correlated subqueries. It also uses fewer temporary tables.

In this section, we will cover all SQL Server 2014 analytic functions.

Ranking functions

We use ranking functions to return the ranking value for the rows within a query result set or partition. The rows in a partition can receive the same ranking value, which depends on...