SoftOve

Database Tools for SQL Server

Introduction to Window Functions in T-SQL

With Window Functions you can do additional queries on the result of your main query, or even a part of the result. In one way they are very similar to aggregate functions but with the main difference that you make the calculation on the rows from your main query.

Using the same example and values as in my previous article Structured and recursive SQL using WITH I will show you a first example.

 /* Add some employees to use with the example. */
 DECLARE @emps TABLE (emp_number INT, emp_name NVARCHAR(100), emp_department VARCHAR(50), emp_salary INT)
 INSERT INTO @emps VALUES(1, 'Anders Andersson', 'Sales', 20000)
 INSERT INTO @emps VALUES(2, 'Bengt Bengtsson', 'Sales', 20000)
 INSERT INTO @emps VALUES(3, 'Claes Claesson', 'Sales', 21000)
 INSERT INTO @emps VALUES(4, 'David Davidsson', 'Development', 21000)
 INSERT INTO @emps VALUES(5, 'Erik Eriksson', 'Development', 23000)
 INSERT INTO @emps VALUES(6, 'Fredrik Fredriksson', 'Development', 24000)
 INSERT INTO @emps VALUES(7, 'Hans Hansson', 'Administration', 21000)
 INSERT INTO @emps VALUES(8, 'Gustav Gustavsson', 'Administration', 22000)
 INSERT INTO @emps VALUES(9, 'Ingvar Ingvarsson', 'Administration', 24000)
 
 
 /* Get each employee and their salaries compared to the averages. */
 SELECT 
 	emp_name
 	,emp_department
 	,emp_salary
 	,emp_salary - AVG(emp_salary) OVER (PARTITION BY emp_department) AS emp_salary_compared_to_department_average
 	,emp_salary - AVG(emp_salary) OVER () AS emp_salary_compared_to_company_average
 FROM
 	@emps


In the exemple above we calculated the average salary per department and for all rows in the result, in this case the whole company.

AVG(emp_salary) OVER (PARTITION BY emp_department)
AVG(emp_salary) OVER ()

Apart from the ordinary aggregate functions AVG(), SUM(), COUNT(), MIN() and MAX() there are a couple more. So far I have only used two of them, RANK() and ROW_NUMBER(). In the example below I show you the difference between these two.

 /* Get each employee and rank their salaries within their department and in the company. */
 SELECT 
 	emp_name
 	,emp_department
 	,emp_salary
 	,RANK() OVER (ORDER BY emp_salary DESC) AS emp_salary_company_rank
 	,ROW_NUMBER() OVER (ORDER BY emp_salary DESC) AS emp_salary_company_row_number
 	,RANK() OVER (PARTITION BY emp_department ORDER BY emp_salary DESC) AS emp_salary_department_rank
 	,ROW_NUMBER() OVER (PARTITION BY emp_department ORDER BY emp_salary DESC) AS emp_salary_department_row_number
 FROM
 	@emps
ORDER BY
	emp_salary_company_row_number


emp_nameemp_departmentemp_salaryemp_salary_company_rankemp_salary_company_row_numberemp_salary_department_rankemp_salary_department_row_number
Fredrik FredrikssonDevelopment240001111
Ingvar IngvarssonAdministration240001211
Erik ErikssonDevelopment230003322
Gustav GustavssonAdministration220004422
Hans HanssonAdministration210005533
Claes ClaessonSales210005611
David DavidssonDevelopment210005733
Anders AnderssonSales200008822
Bengt BengtssonSales200008923

In a future article I will show you some other examples of how you can use the Window Functions to query preceding or following rows. If you cannot wait until then I can recommend the following book by Itzik Ben-Gan, Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions.

David Jackson

Thanks! This is something every serious T-SQL developer should learn and use in their daily work.


Name Comment Send