Naming Window Clause in SQL Server 2022 Window / Oracle 23c Analytical Functions

One of my favorites in database development is "Analytical Functions" ( Oracle ) and "Window Functions" ( SQL Server / MariaDB).

I have written about these functions in the below mentioned posts. 

https://zahirmohideen.blogspot.com/2009/09/sql-treasures.html

https://zahirmohideen.blogspot.com/2009/10/analytics-20.html

https://zahirmohideen.blogspot.com/2009/11/analytics-20-part-ii.html

https://zahirmohideen.blogspot.com/2012/09/difference-between-percentilecont-and.html

https://zahirmohideen.blogspot.com/2013/01/book-sql-server-window-functions.html

https://zahirmohideen.blogspot.com/2018/10/windowing-clause-why-it-matters.html

In MS SQL Server 2022 , now we have the option of "labelling" or "naming" the Window.

This improved readability , especially , when the same windowing clause is referenced in the query multiple times. 

Here is the sample script to create a table and populate few records.

CREATE TABLE 

    emp

    ( 

        eid        INT ,

        lastname   VARCHAR(50),

        firstname  VARCHAR(50),

        department VARCHAR(50),

        salary     NUMERIC(18,2) ,

        hiredate   DATE 

    );

INSERT INTO 

    emp VALUES 

    ( 

        101 , 

        'Doe' , 

        'Jane', 

        'Research', 

        10000 , 

        '1971-08-16' 

    );

INSERT INTO 

    emp VALUES 

    ( 

        102 , 

        'Doe' , 

        'John', 

        'HR', 

        7500 , 

        '1975-03-09' 

    );

INSERT INTO 

    emp VALUES 

    ( 

        103 , 

        'Amin' , 

        'Al', 

        'Research', 

        10300 , 

        '1985-05-14' 

    );

INSERT INTO 

    emp VALUES 

    ( 

        105 , 

        'Kumar' , 

        'Palani', 

        'Faculty Development', 

        9175 , 

        '1985-05-14' 

    );

Here is the sample table "emp" with 4 records in it. 

Here is the sample table "emp" with 4 records in it. 

In the following example , I am naming each window clause a name and reusing that window wherever possible. 

This simple enhancement leads to readability , easier to understand. 

SELECT 

    e.* ,

    MIN(e.salary) OVER win_dep   AS Lowest_Salary_Department ,

    MIN(e.salary) OVER win_all   AS Lowest_Salary_OverAll,

    MAX(e.salary) OVER win_dep   AS Higher_Salary_Department ,

    MAX(e.salary) OVER win_all   AS Higher_Salary_OverAll,

    first_value(e.hiredate) 

                 OVER win_emp AS First_Hired_Date_Department

FROM 

    emp e 

    WINDOW 

    win_emp AS (PARTITION BY department ORDER BY hiredate ASC),

    win_dep AS (PARTITION BY department ),

    win_All AS ( PARTITION BY (1) );

This is equivalent to 

SELECT 

    e.* ,

    MIN(e.salary) OVER ( 

                    PARTITION BY 

                        department ) AS Lowest_Salary_Department ,

    MIN(e.salary) OVER ( 

                    PARTITION BY 

                        (1) ) AS Lowest_Salary_OverAll,

    MAX(e.salary) OVER ( 

                    PARTITION BY 

                        department 

                    ORDER BY 

                        hiredate ASC) AS Higher_Salary_Department ,

    MAX(e.salary) OVER ( 

                    PARTITION BY 

                        (1) ) AS Higher_Salary_OverAll,

    first_value(e.hiredate) OVER ( 

                              PARTITION BY 

                                  department 

                              ORDER BY 

                                  hiredate ASC) AS First_Hired_Date_Department

FROM 

    emp e;

Updated on 04/18/2024

By looking at the 23c new features , learn that this feature is available in Oracle 23c as well.

Here is the script ( identical to SQL Server 2023) . 

SQL> select banner from v$version ;

BANNER

--------------------------------------------------------------------------------

Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release

SQL> desc emp;

 Name    Null?    Type

 ----------------------------------------- -------- ----------------------------

 EID     NUMBER(38)

 LASTNAME     VARCHAR2(50)

 FIRSTNAME     VARCHAR2(50)

 DEPARTMENT     VARCHAR2(50)

 SALARY     NUMBER(18,2)

 HIREDATE     DATE

SQL> select * from emp;

       EID LASTNAME    FIRSTNAME    DEPARTMENT    SALARY HIREDATE

---------- --------------- --------------- -------------------- --------- ---------

       101 Doe    Jane    Research 10000.00 16-AUG-71

       102 Doe    John    HR   7500.00 03-SEP-75

       103 Amin    Al    Research 10300.00 14-MAY-85

       105 Kumar    Palani    Faculty Development   9175.00 14-MAY-85

SQL> SELECT 

    e.* ,

    MIN(e.salary) OVER win_dep   AS Lowest_Salary_Department ,

    MIN(e.salary) OVER win_all   AS Lowest_Salary_OverAll,

    MAX(e.salary) OVER win_dep   AS Higher_Salary_Department ,

    MAX(e.salary) OVER win_all   AS Higher_Salary_OverAll,

    first_value(e.hiredate) 

     OVER win_emp AS First_Hired_Date_Department

FROM 

    emp e 

    WINDOW 

    win_emp AS (PARTITION BY department ORDER BY hiredate ASC),

    win_dep AS (PARTITION BY department ),

    win_All AS ( PARTITION BY (1) );  

       EID LASTNAME    FIRSTNAME    DEPARTMENT    SALARY HIREDATE  LOWEST_SALARY_DEPARTMENT LOWEST_SALARY_OVERALL HIGHER_SALARY_DEPARTMENT HIGHER_SALARY_OVERALL FIRST_HIR

---------- --------------- --------------- -------------------- --------- --------- ------------------------ --------------------- ------------------------ --------------------- ---------

       105 Kumar    Palani    Faculty Development   9175.00 14-MAY-85 9175       7500        9175     10300 14-MAY-85

       102 Doe    John    HR   7500.00 03-SEP-75 7500       7500        7500     10300 03-SEP-75

       101 Doe    Jane    Research 10000.00 16-AUG-71        10000       7500       10300     10300 16-AUG-71

       103 Amin    Al    Research 10300.00 14-MAY-85        10000       7500       10300     10300 16-AUG-71

Comments Welcome!

Book : SQL Server Window Functions

One of my favorite tool-set in Oracle database  is Analytical functions . I used it , wherever appropriate . Most of time , it has solved my problems , which otherwise been done by procedural logic ( aka PL/SQL ) .

Analytical functions has been implemented in Oracle since 1993 - almost  two decades.

You might wonder  ... the title says .... SQL Server .. and this guy talks about Oracle .

With the release of SQL Server 2012  ,  SQL Server has almost all the analytical functions as Oracle.

Currently , I am reading the book "Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions" [ Microsoft Press]. It is pretty interesting and easy read. This book has lot of new stuff for T-SQL developers.  I was surprised to see the name of Oracle Cheif Technogist name ( Tom Kyte ) in one of the chapters [ side notes - where the author mentions about the proposal of new analytical functionality].


I wish both Oracle / MS maintains the same terminology on these functions ( instead of calling it as "analytical " or " window" functions , settle on one nomenclature ) .

Equivalent of Oracle's rownum in SQL Server.

In oracle , we use rownum to display the running number for the result set . There is no direct equivalent in SQL Server . From SQL Server 2005 , it is easy to simulate this functionality with the help of Window Functions ( SQL Server ) / Analytical Functions ( Oracle) .

ORACLE
=======

SQL> set pagesize 90
SQL> select ename , rownum from scott.emp ;

ENAME          ROWNUM
---------- ----------
SMITH               1
ALLEN               2
WARD                3
JONES               4
MARTIN              5
BLAKE               6
CLARK               7
SCOTT               8
KING                9
TURNER             10
ADAMS              11
JAMES              12
FORD               13
MILLER             14

14 rows selected.


SQL Server
=========

C:\Users\zahir>sqlcmd -W
1> use EXSQL2008R2
2> go
Changed database context to 'ExSQL2008R2'.
1>
2> Select top 10 name into t from master.sys.tables;
3> go

1> drop table t;
2> go

1> Select top 10 name into t from master.sys.tables;
2> go

(6 rows affected)
1> Select * from t;
2> go
name
----
spt_fallback_db
spt_fallback_dev
spt_fallback_usg
spt_monitor
spt_values
MSreplication_options

(6 rows affected)





We use ROW_NUMBER function to get the desired result .

1> Select name ,
2> ROW_NUMBER() over ( partition by 1 order by (Select 1)  asc) as rownum
3> from t;
4> go
name rownum
---- ------
spt_fallback_db 1
spt_fallback_dev 2
spt_fallback_usg 3
spt_monitor 4
spt_values 5
MSreplication_options 6

(6 rows affected)

Comments Welcome.