Cyber Monday Deal : Flat 30% OFF! + free self-paced courses  - SCHEDULE CALL

- SQL Server Blogs -

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

Rank

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

department

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

row#

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

depaetment

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

bussiness

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

department

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   

bussiness

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

NTILE

Purpose of SQL Server Windows Function

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

Name

Description

CUME_DIST

  • Calculate the cumulative distribution of a value in a set of values

DENSE_RANK

  • Assign a rank value to each row within a partition of a result, with no gaps in rank values.

FIRST_VALUE

  • Get the value of the first row in an ordered partition of a result set.

LAG

  • Provide access to a row at a given physical offset that comes before the current row.

LAST_VALUE

  • Get the value of the last row in an ordered partition of a result set.

LEAD

  • Provide access to a row at a given physical offset that follows the current row.

NTILE

  • Distribute rows of an ordered partition into a number of groups or buckets

PERCENT_RANK

  • Calculate the percent rank of a value in a set of values.

RANK

  • Assign a rank value to each row within a partition of a result set

ROW_NUMBER

  • Assign a unique sequential integer to rows within a partition of a result set, the first row starts from 1.

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.

Name

Brief Description

Link

LearnSQL.com

https://learnsql.com/

SQL Fidle

  • An online site where one can practice writing SQL Queries

http://sqlfiddle.com/

SQLZoo

  • Provides a dictionary of different sql commands with their use.

https://sqlzoo.net/

W3Resource

  • Works as an online book for different SQL commands and use.

https://www.w3resource.com/

SQL Windows Functions in Microsoft Certification

Certificate

Exam No

Course

MTA: Database Fundamentals

98-364

  • Understanding core database concepts
  • Create database objects
  • Manipulate data
  • Understand data storage
  • Administer a database

Microsoft Azure Data Fundamentals

DP-900

  • Describe core data concepts (15-20%)
  • Describe how to work with relational data on Azure (25-30%)
  • Describe how to work with non-relational data on Azure (25-30%)
  • Describe an analytics workload on Azure (25-30%)

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. 


     user

    JanBask Training

    A dynamic, highly professional, and a global online training course provider committed to propelling the next generation of technology learners with a whole new way of training experience.


  • fb-15
  • twitter-15
  • linkedin-15

Comments

  • J

    Jax Williams

    This is a very nice informative guide, really helpful!

     Reply
    • logo16

      JanbaskTraining

      Glad you found this useful! For more such insights on your favourite topics, do check out JanBask Training Blogs and keep learning with us!

  • A

    Amari Jones

    I am learning SQL queries these days, I would definitely try these tools you have mentioned.

     Reply
    • logo16

      JanbaskTraining

      Glad you found this useful! For more such insights on your favourite topics, do check out JanBask Training Blogs and keep learning with us!

  • Z

    Zane Brown

    How to apply for this Microsoft Azure Data fundamental exam. Can you guide me on this? I would like to take this certification as I am into learning SQL server.

     Reply
    • logo16

      JanbaskTraining

      Thank you so much for your comment, we appreciate your time. Keep coming back for more such informative insights. Cheers :)

  • E

    Emilio Davis

    hi, where can I apply for the SQL course? Can anyone from your team help me apply for it?

     Reply
    • logo16

      JanbaskTraining

      Hello, JanBask Training offers online training to nurture your skills and make you ready for an amazing career run. Please write to us in detail at help@janbasktraining.com. Thanks!

  • K

    Knox Miller

    Nice information. Can you help me find some SQL server interview questions & answers often asked in the interviews.

     Reply
    • logo16

      JanbaskTraining

      Glad you found this useful! For more such insights on your favourite topics, do check out JanBask Training Blogs and keep learning with us!

Trending Courses

salesforce

Cyber Security

  • Introduction to cybersecurity
  • Cryptography and Secure Communication 
  • Cloud Computing Architectural Framework
  • Security Architectures and Models
salesforce

Upcoming Class

0 day 14 Dec 2024

salesforce

QA

  • Introduction and Software Testing
  • Software Test Life Cycle
  • Automation Testing and API Testing
  • Selenium framework development using Testing
salesforce

Upcoming Class

2 days 16 Dec 2024

salesforce

Salesforce

  • Salesforce Configuration Introduction
  • Security & Automation Process
  • Sales & Service Cloud
  • Apex Programming, SOQL & SOSL
salesforce

Upcoming Class

0 day 14 Dec 2024

salesforce

Business Analyst

  • BA & Stakeholders Overview
  • BPMN, Requirement Elicitation
  • BA Tools & Design Documents
  • Enterprise Analysis, Agile & Scrum
salesforce

Upcoming Class

0 day 14 Dec 2024

salesforce

MS SQL Server

  • Introduction & Database Query
  • Programming, Indexes & System Functions
  • SSIS Package Development Procedures
  • SSRS Report Design
salesforce

Upcoming Class

-1 day 13 Dec 2024

salesforce

Data Science

  • Data Science Introduction
  • Hadoop and Spark Overview
  • Python & Intro to R Programming
  • Machine Learning
salesforce

Upcoming Class

0 day 14 Dec 2024

salesforce

DevOps

  • Intro to DevOps
  • GIT and Maven
  • Jenkins & Ansible
  • Docker and Cloud Computing
salesforce

Upcoming Class

3 days 17 Dec 2024

salesforce

Hadoop

  • Architecture, HDFS & MapReduce
  • Unix Shell & Apache Pig Installation
  • HIVE Installation & User-Defined Functions
  • SQOOP & Hbase Installation
salesforce

Upcoming Class

6 days 20 Dec 2024

salesforce

Python

  • Features of Python
  • Python Editors and IDEs
  • Data types and Variables
  • Python File Operation
salesforce

Upcoming Class

7 days 21 Dec 2024

salesforce

Artificial Intelligence

  • Components of AI
  • Categories of Machine Learning
  • Recurrent Neural Networks
  • Recurrent Neural Networks
salesforce

Upcoming Class

0 day 14 Dec 2024

salesforce

Machine Learning

  • Introduction to Machine Learning & Python
  • Machine Learning: Supervised Learning
  • Machine Learning: Unsupervised Learning
salesforce

Upcoming Class

13 days 27 Dec 2024

salesforce

Tableau

  • Introduction to Tableau Desktop
  • Data Transformation Methods
  • Configuring tableau server
  • Integration with R & Hadoop
salesforce

Upcoming Class

6 days 20 Dec 2024

Interviews