Windows SQL Function-All you Need to Know

Introduction

A SQL Server window function performs a calculation on a set of table rows that are somehow related to the current row. This is corresponding to the kind of calculation that may be completed with an aggregate function. But unlike normal aggregate functions, using a SQL Server window function does not result in the rows being grouped into a single output row - the rows keep their identities separate. Behind the scenes, the SQL Server window function can access more than the current row of query result.

In the Following Paragraphs We Will Discuss the Following Points

• Different types of Windows SQL Server functions
• Windows SQL Server functions with examples
• Purpose of Windows SQL Server
• function 
•  How to train with Windows SQL Server online function
• How can we use the knowledge gained in this article on SQL Server Windows functionality for Microsoft certification

Different Types of SQL Server Windows Function

In the following paragraphs, we will describe the above mentioned SQL Windows function in detail with examples. We will be using the Adventureworks database for all of the examples below. You can download the database from the link below depending on the version of SQL Server you are using to practice the Windows SQL function.

Windows SQL Server Functions with Examples

CUME_DIST

CUME_DIST calculates the cumulative distribution of a value in a group of values. The range of values ​​returned by CUME_DIST is> 0 to andlt; = 1. The equilibrium values ​​always return the same cumulative distribution value.

The syntax is as below

SELECT SalesOrderID, OrderQty,

CUME_DIST() OVER(ORDER BY SalesOrderID) AS CDist

FROM Sales.SalesOrderDetail

WHERE SalesOrderID IN (43670, 43669, 43667, 43663)

ORDER BY CDist DESC

The output is as below.

RANK

This returns the rank of each row within the partition of a result set. The rank of a row is one plus the number of ranks precedes the row in question.

The syntax is like this

SELECT i.ProductID, p.Name, i.LocationID, i.Quantity  

    ,RANK() OVER   

    (PARTITION BY i.LocationID ORDER BY i.Quantity DESC) AS Rank  

FROM Production.ProductInventory AS i   

INNER JOIN Production.Product AS p   

    ON i.ProductID = p.ProductID  

WHERE i.LocationID BETWEEN 3 AND 4  

ORDER BY i.LocationID;  

The output is

DENSE_RANK

This SQL Server windows function returns the rank of each row within a result set partition, with no gaps in the ranking values. The rank of a specific row is equal to one plus the number of distinct rank values preceding that specific row.

The syntax is like this

SELECT i.ProductID, p.Name, i.LocationID, i.Quantity  

    ,DENSE_RANK() OVER   

    (PARTITION BY i.LocationID ORDER BY i.Quantity DESC) AS Rank  

FROM Production.ProductInventory AS i   

INNER JOIN Production.Product AS p   

    ON i.ProductID = p.ProductID  

WHERE i.LocationID BETWEEN 3 AND 4  

ORDER BY i.LocationID;  

The output is

SELECT Department, LastName, Rate,   

       CUME_DIST () OVER (PARTITION BY Department ORDER BY Rate) AS CumeDist,   

       PERCENT_RANK() OVER (PARTITION BY Department ORDER BY Rate ) AS PctRank  

FROM HumanResources.vEmployeeDepartmentHistory AS edh  

    INNER JOIN HumanResources.EmployeePayHistory AS e    

    ON e.BusinessEntityID = edh.BusinessEntityID  

WHERE Department IN (N'Information Services',N'Document Control')   

ORDER BY Department, Rate DESC;

The output is

ROW_NUMBER

ROW_NUMBER Numbers the output of a result set. Specifically, it returns the sequential number of a row within a partition of a result set, starting at 1 for the first row of each partition.

The syntax is like this

SELECT 

  ROW_NUMBER() OVER(ORDER BY name ASC) AS Row#,

  name, recovery_model_desc

FROM sys.databases 

WHERE database_id

The output is

FIRST_VALUE

This FIRST_VALUE function will return the First Value in each partition.

The syntax as below

SELECT SalesOrderID,CustomerID,

YEAR(OrderDate)as[Year],TotalDue,

FIRST_VALUE(CONVERT(DATE,OrderDate))

OVER (Partition BY Year(OrderDate) ORDER BY OrderDate) AS FirstOrderDate

FROM Sales.SalesOrderHeader

WHERE CustomerID=29994

The output is

LAG

We use a Lag() function to access previous rows data as per defined offset value.

The syntax is like this

SELECT BusinessEntityID, YEAR(QuotaDate) AS SalesYear, SalesQuota AS CurrentQuota,   

       LAG(SalesQuota, 1,0) OVER (ORDER BY YEAR(QuotaDate)) AS PreviousQuota  

FROM Sales.SalesPersonQuotaHistory  

WHERE BusinessEntityID = 275

The output looks like below

LAST_VALUE

This SQL Windows function returns the last value in an ordered set of values.

The syntax is a below.

SELECT Department, LastName, Rate, HireDate,   

    LAST_VALUE(HireDate) OVER (PARTITION BY Department ORDER BY Rate) AS LastValue  

FROM HumanResources.vEmployeeDepartmentHistory AS edh  

INNER JOIN HumanResources.EmployeePayHistory AS eph    

    ON eph.BusinessEntityID = edh.BusinessEntityID  

INNER JOIN HumanResources.Employee AS e  

    ON e.BusinessEntityID = edh.BusinessEntityID  

WHERE Department IN (N'Information Services',N'Document Control'); 

The output is as below

LEAD

LEAD() is a SQL window function that provides access to a row at a specified physical offset which follows the current row.

The syntax is like this

SELECT BusinessEntityID, YEAR(QuotaDate) AS SalesYear, SalesQuota AS CurrentQuota,   

    LEAD(SalesQuota, 1,0) OVER (ORDER BY YEAR(QuotaDate)) AS NextQuota  

FROM Sales.SalesPersonQuotaHistory  

WHERE BusinessEntityID = 275   

NTILE

NTILE() function in SQL Server is a window function that distributes rows of an ordered partition into a pre-defined number of roughly equal groups.

SELECT p.FirstName, p.LastName  

    ,NTILE(4) OVER(ORDER BY SalesYTD DESC) AS Quartile  

    ,CONVERT(NVARCHAR(20),s.SalesYTD,1) AS SalesYTD  

    , a.PostalCode  

FROM Sales.SalesPerson AS s   

INNER JOIN Person.Person AS p   

    ON s.BusinessEntityID = p.BusinessEntityID  

INNER JOIN Person.Address AS a   

    ON a.AddressID = p.BusinessEntityID  

WHERE TerritoryID IS NOT NULL   

    AND SalesYTD <> 0;  

The Output is as Below

Purpose of SQL Server Windows Function

The following table details the different types of windows functions available in SQL Server.

How to Practice SQL Server Windows Function Online

Practice makes a man perfect. For practice, it is always best to download and install the SQL Server Database Engine on your laptop or desktop computer. But if you are a tech savvy person and want to practice uncontrollable SQL queries whenever you are free, here are some online tools where you can try practicing SQL queries online.

SQL Windows Functions in Microsoft Certification

Conclusion

This particular article details the different types of Windows SQL functions and their use. It also provides information on how to practice them online and also how to use the skills acquired through learning Windows SQL function in Microsoft certifications. This is just a small step to familiarize you with the Windows SQL function. This will encourage you to venture deeper into the world of Windows SQL functions and learn more.