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!