Here are some issues which may catch you out from time to time. In each case Sql Server and Oracle, or Oracle Sql and PL/Sql, or versions of Oracle, work differently. If you want to see what Sql Server does with the queries, please run the following code:
CREATE TABLE DUAL(DUMMY CHAR(1) PRIMARY KEY(DUMMY) CHECK(DUMMY = 'X'));INSERT INTO DUAL VALUES('X') ;

Let’s take a  GROUP BY statement that hasn’t been coded correctly:
SELECT C1
,C2
FROM
(
SELECT DUMMY AS C1, DUMMY AS C2 FROM DUAL
UNION ALL
SELECT DUMMY, DUMMY FROM DUAL
) T1
GROUP BY C1


You’ll see the following errors:
Sql Server: Msg 8120, Level 16, State 1, Line 19
Column ‘T1.C2’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Oracle Sql:ORA-00979: not a GROUP BY expression
00979. 00000 –  “not a GROUP BY expression”

No problem then – but what about PL/Sql?
DECLARE CURSOR
cur_TestGroupBy
IS
SELECT C1
,C2
FROM
(
SELECT DUMMY AS C1, DUMMY AS C2 FROM DUAL
UNION ALL
SELECT DUMMY, DUMMY FROM DUAL
) T1
GROUP BY C1;
v_TestGroupBy cur_TestGroupBy%ROWTYPE;
BEGIN
NULL;
END;

PL/Sql: anonymous block completed

So in PL/Sql you don’t get full GROUP BY statement parsing at compile time. What we’ve demonstrated with an anonymous block also applies to functions and procedures of course. It won’t cause you problems very often, but if you’ve hurriedly fixed a bug in a three-hour-long batch process and there are GROUP BY statements involved, you might want to test carefully before you kick off the re-run. In Sql Server stored procedures you’ll get the compile-time error by the way.

Another example:
WITH TestTable
As
(
SELECT DUMMY AS C1, DUMMY AS C2 FROM DUAL
UNION ALL
SELECT DUMMY, DUMMY FROM DUAL
)
SELECT *
FROM TestTable T1
JOIN TestTable T2
ON T1.C1 = T2.C1
--where's the alias on the column below?--
WHERE C1 = 'X'

Sql Server: Msg 209, Level 16, State 1, Line 12
Ambiguous column name ‘C1’.

Oracle 11g: ORA-00918: column ambiguously defined

In Oracle 10g, it doesn’t work like that, at least not in some circumstances. I once spent a long time debugging a complex query which was giving very odd results in 10g. The cause turned out to be a column which was ambiguously defined; it was missing an alias to one of the joined tables. So if you’re still in 10g, be aware that this may happen, and also that you’ll have to fix it if you upgrade to 11g.

Here’s another difference between Sql Server and Oracle. Take the following query, very simple but still valid for comparison:
--DROP VIEW V1
CREATE VIEW V1
AS
SELECT *
FROM
(
SELECT 'C' AS DUMMY FROM DUAL
UNION
SELECT 'B' FROM DUAL
UNION
SELECT 'A' FROM DUAL
--order by position is deprecated but makes the query portable to Oracle--
ORDER BY 1
) D1

Sql Server 2014:Msg 1033, Level 15, State 1, Line 22
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

In Oracle there’s no problem, but why would we want the ordering inside the view anyway? Well, we normally wouldn’t. It just might be convenient though to have the ORDER BY statement “baked in”, especially if it was complex. So how can it be made to work in Sql Server? The error message gives an answer. In Sql Server 2014 we could consider this:
--DROP VIEW V1
CREATE VIEW V1
AS
SELECT *
FROM
(
SELECT 'C' AS DUMMY FROM DUAL
UNION
SELECT 'B' FROM DUAL
UNION
SELECT 'A' FROM DUAL
--order by position is deprecated but makes the query portable to Oracle--
ORDER BY 1 OFFSET 0 ROWS
) D1

The OFFSET statement is quite new, so in your version of Sql Server you might have to change the “SELECT” to “SELECT 100 PERCENT”. In the quick testing that I did, it appears that you have to to make the change to each part of the UNION. That gets it to compile but it doesn’t sort any more, so it’s pointless. This wouldn’t apply to views not containing UNIONs.

The complexities of getting it to work suggest it’s probably best to let Sql Server have its own way and leave out the ORDER BY. That’s what I do anyway.