SQL Server Window Functions are a powerful feature that allows you to perform calculations across a set of rows in a result set. These functions operate on a “window” of rows defined by a partition and an ordering, and they can provide aggregated results, rankings, row numbering, and more.
SQL Server Window Functions calculate an aggregate value based on a group of rows and return multiple rows for each group.
Types of Window Functions
- Aggregate Window Functions: SUM(), MAX(), MIN(), AVG(), COUNT()
- Ranking Window Functions: RANK(), DENSE_RANK(), ROW_NUMBER(), NTILE()
- Value Window Functions: LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE()
Basic Syntax:
SELECT column_name1, window_function(column_name2),
OVER([PARTITION BY column_name1] [ORDER BY column_name3])
AS new_column from table_name;
Window_function = any aggregate or ranking function
column_name1 = column to be selected
column_name2 = column on which window function is to be applied
column_name3 = column on whose basis partition of rows is to be done
new_column = Name of new column
table_name=Name of table
Here are some commonly used SQL Server Window Functions:
| Name | Description |
| SUM() | Calculates the sum of a set of values within a specified column |
| MAX() | Retrieves the maximum value within a specified column |
| MIN() | Retrieves the minimum value within a specified column |
| AVG() | Calculates the average (mean) value of a specified column |
| COUNT() | Calculates the number of rows or non-null values in a specified column |
| RANK | Assign a rank value to each row within a partition of a result set |
| DENSE_RANK | Assign a rank value to each row with in a partition of a result, with no gaps in rank values. |
| PERCENT_RANK | Calculate the percent rank of a value in a set of values |
| ROW_NUMBER | Assign a unique sequential integer to rows within a partition of a result set, the first row starts from 1 |
| NTILE | Distribute rows of an ordered partition into a number of groups or buckets |
| LAG | Provide access to a row at a given physical offset that comes before the current row. |
| LEAD | Provide access to a row at a given physical offset that follows the current row. |
| FIRST_VALUE | Get the value of the first row in an ordered partition of a result set |
| LAST_VALUE | Get the value of the last row in an ordered partition of a result set. |
| CUME_DIST | Calculate the cumulative distribution of a value in a set of values |
These are just a few examples of the window functions available in SQL Server. Each function has specific syntax and usage requirements, which can be found in the SQL Server documentation for the corresponding version of the software. Window functions can greatly enhance the analytical capabilities of SQL queries by allowing complex calculations and analysis across rows.