SQL Server WINDOW functions are a powerful feature that allows you to perform calculations and aggregate functions over a subset of rows in a result set, rather than over the entire result set. Window functions were introduced in SQL Server 2005, and have been enhanced in subsequent releases.

Window functions are typically used in scenarios where you need to calculate a running total, rolling average, or rank of rows based on specific criteria. With window functions, you can perform these calculations without the need for temporary tables or multiple queries, resulting in more efficient and concise code.

Syntax

The syntax for using window functions in SQL Server is as follows:

<window function> OVER (
[PARTITION BY <partition column> [, <partition column>...]]
[ORDER BY <order column> [ASC|DESC] [, <order column> [ASC|DESC]]...]
[ROWS <frame specification>]
)

The key components of this syntax are:

window function the name of the window function to be used (e.g. SUM, AVG, ROW_NUMBER).
OVER the keyword that starts the window function definition.
PARTITION BY optional clause that specifies the columns used to partition the result set into separate groups, each of which will have its own window.
ORDER BY optional clause that specifies the order in which the rows in each partition will be processed by the window function.
ROWS optional clause that specifies the range of rows to include in the window frame (e.g. preceding, following, current row).

Example

SELECT col1, col2, col3,
SUM(col4) OVER (PARTITION BY col2 ORDER BY col1 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS col4_sum
FROM my_table

Here are some examples of common window functions in SQL Server:

ROW_NUMBER assigns a unique number to each row in the result set, based on the specified order.
RANK assigns a rank to each row in the result set, based on the specified order (ties receive the same rank).
DENSE_RANK assigns a rank to each row in the result set, based on the specified order (ties receive the same rank, but the next rank is skipped).
SUM calculates the sum of a numeric column over a window.
AVG calculates the average of a numeric column over a window.
MAX returns the maximum value of a column over a window.
MIN returns the minimum value of a column over a window.

In conclusion, SQL Server Window functions are a powerful tool that can help you perform complex calculations over a subset of rows in a result set. By using window functions, you can avoid the need for temporary tables or multiple queries, resulting in more efficient and concise code.