Difference between Oracle SQL Query vs Microsoft SQL Server 2008 or Sybase

Oracle and Microsoft SQL Server are two of the most popular database but they are very different with each other and if you are migrating SQL queries or database, tables from Oracle 11g database to Microsoft SQL Server 2008 then you are bound to face some issues. Main reason of these porting issues are features which are supported and exists in Oracle database, but not available in Microsoft SQL Server 2008 like SEQUENCE, Order by clause in subqueries, and derived tables, derived table without name etc. I am sure there are few more and it will surface based upon different database objects you are using in your tables and queries. On another hand SQL engine for SQL Server and Sybase are very much similar, at least syntactically, and if you are migrating queries from SQL Server to Sybase you can do that without much hassle, of course, there will be slight changes but not as much like Oracle.

So, if you are migrating from Oracle to Sybase or SQL Server it's most likely same job and you should first start with either SQL Server or Sybase ASE and then later migrate them from each other. 

In this Oracle and SQL Server tutorial, we will see a couple of examples, where Oracle and SQL Server are different and how to change those SQL queries so that it can run on Microsoft SQL Server. 

By the way, I have also written a couple of post on queries like 10 ways to use SQL SELECT queries and Don’t delete, truncate it. If you like reading more on SQL queries than those are for you.

Migrating SQL queries from Oracle to SQL Server

As I said, we faced three main problems while migrating our SQL queries from Oracle 11g to SQL Server 2008, those are related to SEQUENCE object, order by clause in subqueries and using anonymous derived tables. Let’s see them one by one.

1) Order by in subquery or derived table :

Oracle supports order by clause in a subquery and derived tables, but when you try to run the same query, which is working fine in Oracle, in SQL Server or Sybase, you will get following error because they don’t support order by clause on derived table or subquery.

Error : The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions unless TOP or FOR XML is also specified.

Solution:
In order to solve this problem you need to remove order by clause from subquery and derived table and move those to the main result set, most of the time you can do that but if you can't then you probably need to rewrite the whole query.

See  Mastering Oracle SQL for more details on using order by clause on subquery or derived table in Oracle.

Below query will work in Oracle but will not work in Microsoft SQL Server, as it is using order by clause in subquery:

select CUSTOMER_ID,CUSTOMER_ADDRESS from CUSTOMER 
where CUSTOMER_IDS IN 
    (select SELLER_ID from TRANSACTIONS where ITME_TYPE='PC' 
     order by PURCHASE_DATE)

In order to make it work we need to remove order by clause from subquery and like below:

select CUSTOMER_ID,CUSTOMER_ADDRESS from CUSTOMER 
where CUSTOMER_IDS IN 
    (select SELLER_ID from TRANSACTIONS where ITME_TYPE='PC')

if this is not what you desire then you need to rewrite this query using SQL Joins

2) Derived table without name :

Second difference I found between Oracle and SQL Server query is that oracle allows you to query derived table without a specifying name but SQL Server doesn't allow. Look at following query, which works perfectly in Oracle but gives syntax error in SQL Server :

select count(*) from (
    select BUYER_ID from TRANSACTIONS where SELLER_ID= 'james_2012' and item_type='PC'
    UNION
    select SELLER_ID from TRANSACTIONS where BUYER_ID= 'james_2012' and item_type='PC'
 )

Error: Incorrect syntax near ')'

Solution:
This error was extremely easy to fix, we just need to provide a name to our derived table using "as" keyword, here is the modified SQL query which will work in Sybase and Microsoft SQL Server 2005, 2008 and may be in another version :

select count(*) from (
    select BUYER_ID from TRANSACTIONS where SELLER_ID= 'james_2012' and item_type='PC'
    UNION
    select SELLER_ID from TRANSACTIONS where BUYER_ID= 'james_2012' and item_type='PC'
 ) as sells

3) SEQUENCE Object:

Another problem we faced while migrating our queries from Oracle to SQL Server is related to SEQUENCE object. We were using Oracle SEQUENCE object to generate automatically incremented id and SEQUENCE are not supported in Microsoft SQL Server 2008 (let me know if it does because I tried to create SEQUENCE and it gives error "Unknown object type 'SEQUENCE' used in a CREATE, DROP, or ALTER statement").

The only solution was to remove the SEQUENCE code and instead use IDENTITY feature of SQL Server, which is similar to sequence and creates automatically incremented column. You provide IDENTITY a seed and an incremental value, default is (1,1) and it automatically generates numbers for yours.

What comes to us as  pleasant surprise was pagination queries, we had some oracle pagination queries in our application which is written using row_num() function and we are thinking about how to migrate them into SQL server, before discovering that SQL Server also supports ROW_NUM() function.

In short Oracle pagination queries runs fine on SQL Server except few changes mentioned above e.g. order by clause inside subquery or derived table.

That's it on migrating SQL queries from Oracle to SQL Server or Sybase ASE database. I am sure there is much more difference between Oracle and SQL Server, which I haven't covered simply because I haven't faced them.

By the way, these were some of the most common changes, you need to make to run your oracle query into SQL server.  If you have also done the same job then please let us know what kind of issue you have faced on SQL Server side and how did you solve it. Hmm, sounds like a good question?