Oracle's Analytical functions Vs SQL Server's Windowing Functions

With the release of SQL Server 2012 , SQL Server has caught up with Oracle's implementation of Analytical functions , there are few subtle differences .

We cannot use a constant in the windowing clause . The workaround is to do declare/ select the constant in the Windowing function.

Here is an example.

ORACLE
======

SQL> drop table emp;
Table dropped.

SQL> Create table emp
  2  ( eid int primary key,
  3    elname  char(10 ) not null,
  4    efname  char(10 ) not null,
  5    deptname char(10)
  6  ) ;
Table created.

SQL> insert into emp values ( 100 , 'Abdul' , 'Hameed' , 'Sales');
1 row created.

SQL> insert into emp values ( 200 , 'Syed' , 'Ishak' , 'Sales');
1 row created.

SQL> insert into emp values ( 300 , 'Zahir' , 'Mohideen' , 'IT');
1 row created.

SQL> Select eid , elname , efname , ROW_NUMBER() over ( partition by deptname  order by 1 ) rn from emp;

       EID ELNAME     EFNAME             RN
---------- ---------- ---------- ----------
       300 Zahir      Mohideen            1
       100 Abdul      Hameed              1
       200 Syed       Ishak               2


SQL Server
==========

C:\>sqlcmd -W
1> use test2012nf
2> go
Changed database context to 'test2012nf'.
1> drop table emp;
2> GO
1> Create table emp
2> ( eid int primary key,
3>   elname  char(10 ) not null,
4>   efname  char(10 ) not null,
5>   deptname char(10)
6> ) ;
7> GO
1> insert into emp values ( 100 , 'Abdul' , 'Hameed' , 'Sales');
2> insert into emp values ( 200 , 'Syed' , 'Ishak' , 'Sales');
3> insert into emp values ( 300 , 'Zahir' , 'Mohideen' , 'IT');
4> GO

(1 rows affected)

(1 rows affected)

(1 rows affected)
1> Select eid , elname , efname , ROW_NUMBER() over ( partition by deptname  order by 1 ) rn from emp;
2> go
Msg 5308, Level 16, State 1, Server ITSQL-012, Line 1
Windowed functions and NEXT VALUE FOR functions do not support integer indices as ORDER BY clause expressions.


Now replace

1> Select eid , elname , efname , ROW_NUMBER() over ( partition by deptname  order by (select 1)  ) rn from emp;
2> go
eid elname efname rn
--- ------ ------ --
300 Zahir      Mohideen   1
100 Abdul      Hameed     1
200 Syed       Ishak      2

(3 rows affected)

Comments Welcome!

FETCH X ROWS in Oracle 12c ( New Feature !)

Oracle 12c has introduced "FETCH" n rows clause in the SELECT statement. Though , it is a "new" feature , Oracle seems to be doing the same good old techniques what the developers were doing for the last decade or so using Analytical functions.

Let us look at the example below.

The highlighted one in the following select statement is one of 12c feature. As it can be seen from the predicate information in the execution plan , Oracle is using ROW_NUMBER function. If you look at the execution plans' predicate information and results , it is pretty much the same.

Have to admit , the new feature shortens the SELECT statement and avoids the subquery .


SQL> select * from scott.emp order by empno fetch first 4 rows only;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20


Execution Plan
----------------------------------------------------------
Plan hash value: 2801941731

----------------------------------------------------------------------------------------
| Id  | Operation                     | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |        |    14 |  1582 |     2   (0)| 00:00:01 |
|*  1 |  VIEW                         |        |    14 |  1582 |     2   (0)| 00:00:01 |
|*  2 |   WINDOW NOSORT STOPKEY       |        |    14 |   532 |     2   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| EMP    |    14 |   532 |     2   (0)| 00:00:01 |
|   4 |     INDEX FULL SCAN           | PK_EMP |    14 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=4)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY "EMP"."EMPNO")<=4)
 

Here is the equivalent SQL from pre-12c .

SQL> Select * from ( Select  e.* , row_number() over ( partition by 1 order by empno) rn from scott.emp e ) where rn <= 4 ;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO         RN
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20          1
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30          2
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30          3
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20          4


Execution Plan
----------------------------------------------------------
Plan hash value: 2801941731

----------------------------------------------------------------------------------------
| Id  | Operation                     | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |        |    14 |  1400 |     2   (0)| 00:00:01 |
|*  1 |  VIEW                         |        |    14 |  1400 |     2   (0)| 00:00:01 |
|*  2 |   WINDOW NOSORT STOPKEY       |        |    14 |   532 |     2   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| EMP    |    14 |   532 |     2   (0)| 00:00:01 |
|   4 |     INDEX FULL SCAN           | PK_EMP |    14 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("RN"<=4)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY "EMPNO")<=4)


Comments Welcome.