References

1. Introduction

The SQL Server provides a suite of powerful tools and features to handle and manipulate data. Among these, SQL Server window functions stand out as essential tools that enable you to perform data analysis and calculations that were difficult or impossible with traditional SQL. This article will guide you through understanding and utilizing these functions, focusing on their use in sqlcmd and DbSchema .

2. Prerequisites

Before starting, ensure you have the following:

Basic knowledge of SQL commands and syntax

A working SQL Server instance

Installed sqlcmd utility and DbSchema

For installation and establishing connection you can read our article SQL Server-How to create a database?

3. What are Window Functions?

SQL Server window functions operate on a set of rows, known as the window , and return a single value for each row from the underlying query. The window specification defines the window in terms of rows relative to the current row and allows partitioning of the result set into groups or partitions .

4. Purpose of Using Window Functions

Window functions provide a way to perform calculations across a set of rows that are related to the current row. This allows for complex analyses such as calculating running totals, averages, or percentages, which would be challenging to perform using standard SQL queries.

5. Restrictions and Permissions

To execute a query that involves window functions, you need SELECT permissions on the target table. The restrictions include:

Window functions can’t be used in a WHERE clause .

The OVER clause can’t be used in a GROUP BY clause.

The window function and window order clause can’t contain alias column names.

6. Advantages and Limitations

Advantages:

Advantages of window functions include:

Simplified queries : Can replace complex subqueries and self-joins.

Improved performance : Less computing resource consumption compared to traditional methods.

Enhanced data analysis : Allow advanced calculations on sets of rows.

Limitations:

Limitations of window functions include:

Can’t be used in WHERE, GROUP BY, or HAVING clauses.

Can’t be nested.

Don’t support the use of window functions in the window order clause.

7. Types of Window Functions

i. Aggregate Window Functions

These are standard SQL aggregate functions but with an OVER clause to operate on a window of rows. Examples include SUM , COUNT , AVG , MIN , and MAX .

ii. Ranking Window Functions

These provide a ranking value to each row in a window, like RANK , DENSE_RANK , ROW_NUMBER , and NTILE .

iii. Value Window Functions

These provide access to data from another row in the same window without the need for a self-join. These include LAG , LEAD , FIRST_VALUE , and LAST_VALUE .

8. How to Use Window Functions in sqlcmd and DbSchema

Let’s take a sample employees table with the following data for illustration:

emp_id dept_id salary hire_date 2020-01-10 2020-06-20 2020-02-15 2020-08-30 2020-03-25 2020-10-05

sqlcmd

Step 1: Open the sqlcmd utility in your command line.

Step 2: Connect to your database:

sqlcmd -S <server_name> -d <database_name> -U <username> -P <password>
Replace ServerName, UserName, Password, and DatabaseName with your actual SQL Server details.
Let’s work with different window functions on this table.
Aggregate Window Function (SUM)
We calculate the cumulative salary for each department (dept_id).
SELECT 
  emp_id, 
  dept_id, 
  salary,
  SUM(salary) OVER (PARTITION BY dept_id ORDER BY hire_date 
                    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 
                    AS cumulative_salary
FROM employees;
Results From the Query:
Following result will be obtained after we run the above query on our sample database table:
emp_id
dept_id
salary
cumulative_salary
10000
12000
12000
Ranking Window Function (RANK)
We rank employees within each department based on salary.
SELECT 
  emp_id, 
  dept_id, 
  salary,
  RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) 
  AS salary_rank
FROM employees;
Results From the Query:
Following result will be obtained after we run the above query on our sample database table:
emp_id
dept_id
salary
salary_rank
Value Window Function (LAG)
We get the salary of the previously hired employee within each department.
SELECT 
  emp_id, 
  dept_id, 
  salary,
  LAG(salary) OVER (PARTITION BY dept_id ORDER BY hire_date) 
  AS prev_salary
FROM employees;
Results From the Query:
Following result will be obtained after we run the above query on our sample database table:
emp_id
dept_id
salary
prev_salary
DbSchema
DbSchema is a user-friendly environment for working with databases, and it has SQL Editor that allows you to write and execute SQL queries. Let’s dive into a step-by-step guide on how to use window functions within DbSchema.

Before we start, let’s consider a sales table in our database, which has the following records:
sale_id
product_id
sale_date
sale_amount
2023-01-15
2023-01-20
2023-02-01
2023-02-10
2023-03-05
2023-03-20
2023-04-05
2023-04-15
2023-05-10
2023-05-20
1. Aggregate Window Function
Let’s calculate the running total of sale_amount for each product_id ordered by sale_date.

Open DbSchema and connect to your database.
Navigate to SQL Editor and open a new window.
Input the following query:
SELECT 
  sale_id, 
  product_id, 
  sale_date,
  sale_amount, 
  SUM(sale_amount) OVER (PARTITION BY product_id ORDER BY sale_date 
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 
                         AS running_total
FROM sales;
Click the Execute button. You will see the results displayed in tabular format below the SQL editor.
Results From the Query:
Following result will be obtained after we run the above query on our sample database table:
The result of the running total of sale_amount for each product_id ordered by sale_date:
sale_id
product_id
sale_date
sale_amount
running_total
2023-01-15
2023-02-01
2023-03-20
2023-05-10
2023-01-20
2023-03-05
2023-04-15
2023-02-10
2023-04-05
2023-05-20
2. Ranking Window Function
Now let’s rank sales records within each product_id based on sale_amount.

In the SQL Editor, type in the following:
SELECT 
  sale_id, 
  product_id, 
  sale_date,
  sale_amount, 
  RANK() OVER (PARTITION BY product_id ORDER BY sale_amount DESC) 
  AS sale_rank
FROM sales;
Click the Execute button. You will see the results in the output panel.
Results From the Query:
Following result will be obtained after we run the above query on our sample database table:
The ranking of sales records within each product_id based on sale_amount:
sale_id
product_id
sale_date
sale_amount
sale_rank
2023-01-15
2023-02-01
2023-03-20
2023-05-10
2023-01-20
2023-03-05
2023-04-15
2023-02-10
2023-04-05
2023-05-20
3. Value Window Function
Let’s get the sale_amount of the previous sale within each product_id.

In the SQL Editor, write the following:
SELECT 
  sale_id, 
  product_id, 
  sale_date,
  sale_amount, 
  LAG(sale_amount) OVER (PARTITION BY product_id ORDER BY sale_date) 
  AS previous_sale_amount
FROM sales;
Click the Execute button. The output panel will display the result of this query.
Results From the Query:
Following result will be obtained after we run the above query on our sample database table:
The sale_amount of the previous sale within each product_id:
sale_id
product_id
sale_date
sale_amount
previous_sale_amount
2023-01-15
2023-02-01
2023-03-20
2023-05-10
2023-01-20
2023-03-05
2023-04-15
2023-02-10
2023-04-05
2023-05-20
Please note that in the case of the LAG function, we have NULL values whenever there isn’t a preceding row within the partition
Note: Please remember to replace the table name, column names, and partition details according to your specific database schema. Window functions are a powerful tool in SQL Server and can provide insights into your data in ways that other methods cannot.
Visually Manage SQL Server using DbSchema
DbSchema is a SQL Server client and visual designer. DbSchema has a free Community Edition, which can be downloaded here.
Key Features of DbSchema:
Following are the key features of DbSchema which distinguish it from other database GUI tools.

9. Conclusion 
SQL Server window functions are a powerful tool for data analysis. By learning to use them in sqlcmd and DbSchema, you can perform complex queries and calculations with relative ease. Ensure you understand the advantages, limitations, and types of window functions to make the most of these tools.

10. References 

Microsoft SQL Server Documentation: Window Functions
DbSchema Documentation: SQL Editor
SQL Server | Microsoft: sqlcmd
                Visually Manage Databases using DbSchema
                
DbSchema is a databases client and visual designer. DbSchema has a free Community Edition, which can be downloaded here.
DbSchema main features include:
                
Interactive Diagrams

                
Design tables, column and foreign keys directly in diagrams, by double-clicking them. Changes will be saved to the design model and, if DbSchema is connected to the database also into the database. More.
                
Simple Connection Dialog

                
Choose the database location, the user and password, and simply get connected. Choose 'Edit Manually' into the JDBC URL combo to enter a custom URL. More.
                
Relational Data Explorer

                
Explore data from multiple tables simultaneously, using foreign keys or virtual foreign keys. Double-click cells to edit the data. More.
                
Query Builder
Create SQL Queries featuring JOINS, GROUP BY, ORDER BY just using the mouse. More.
                
SQL Query Editor
Edit and execute SQL Queries. The editor is autocompletion-enabled. More.
                
Design Schema in Team & Schema Deployment
DbSchema is using the design model, a copy of the schema structure, independent of the database.
The design model can be saved to file and shared in a team.
Connecting to another database you may compare the design model with the database, commit the differences or merge them in the design model. More.
                
Dark Theme

                
Configurable styles & dark theme. More.
                
Many features are available in the free Community edition.
The Pro edition adds capabilities to save the design to the model file, design schema in team and deploy the schema on multiple databases.
                
DbSchema can be downloaded for free. No registration is required.