Independence Day OFFER : Learning is Freedom, Get FLAT 25% OFF  INDEPENDENCE25

- 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

PERCENT_RANK

PERCENT_RANK calculates the relative rank of a row in a row group in SQL Server.

The syntax will be like this

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

  • A good place to learn different types of SQL Queries

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. 

FaceBook Twitter Google+ LinkedIn Pinterest Email

    Sanchayan Banerjee

    I love to learn new things and also like sharing my knowledge with others. As an experienced IT Professional I like to update myself constantly with new and upcoming technologies. The database management system is one of my favorite subjects which I constantly explore.


Comments

  • J

    Jax Williams

    This is a very nice informative guide, really helpful!

     Reply
    • 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
    • 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
    • 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
    • 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
    • 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

AWS

  • AWS & Fundamentals of Linux
  • Amazon Simple Storage Service
  • Elastic Compute Cloud
  • Databases Overview & Amazon Route 53

Upcoming Class

3 days 08 Jul 2022

DevOps

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

Upcoming Class

10 days 15 Jul 2022

Data Science

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

Upcoming Class

3 days 08 Jul 2022

Hadoop

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

Upcoming Class

3 days 08 Jul 2022

Salesforce

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

Upcoming Class

10 days 15 Jul 2022

QA

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

Upcoming Class

3 days 08 Jul 2022

Business Analyst

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

Upcoming Class

3 days 08 Jul 2022

MS SQL Server

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

Upcoming Class

3 days 08 Jul 2022

Python

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

Upcoming Class

3 days 08 Jul 2022

Artificial Intelligence

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

Upcoming Class

11 days 16 Jul 2022

Machine Learning

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

Upcoming Class

24 days 29 Jul 2022

Tableau

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

Upcoming Class

4 days 09 Jul 2022

Search Posts

Reset

Trending Posts

Receive Latest Materials and Offers on SQL Server Course

Interviews