Difference between Oracle SQL Query vs Microsoft SQL Server or Sybase? Answer

Oracle and Microsoft SQL Server are two of the most popular database but they are very different from each other and if you are migrating SQL queries or databases, tables from Oracle database to Microsoft SQL Server 2008 then you are bound to face some issues. The main reason for these porting issues are features that are supported and exists in the Oracle database, but not available in Microsoft SQL Server 2008 like SEQUENCE, Order by clause in subqueries, and derived tables, derived table without a name, etc. I am sure there are a few more and they 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 as Oracle.

So, if you are migrating from Oracle to Sybase or SQL Server it's most likely the 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 they can run on Microsoft SQL Server. 

Migrating SQL queries from Oracle to SQL Server

As I said, we faced three main problems while migrating our SQL queries from Oracle to SQL Server, 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 the 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. 

The below query will work in Oracle but will not work in Microsoft SQL Server, as it is using order by clause in a 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 a name

The second difference I found between Oracle and SQL Server query is that oracle allows you to query derived tables without a specifying name but SQL Server doesn't allow it. Look at the 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 the SEQUENCE object. We were using Oracle SEQUENCE object to generate automatically incremented id and SEQUENCE are not supported in Microsoft SQL Server (let me know if it does because I tried to create SEQUENCE and it gives the 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 the IDENTITY feature of SQL Server, which is similar to sequence and creates automatically incremented columns. You provide IDENTITY a seed and an incremental value, the default is (1,1) and it automatically generates numbers for yours. 

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

In short Oracle, pagination queries run fine on SQL Server except few changes mentioned above like 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 the SQL server.  If you have also done the same job then please let us know what kind of issue you have faced on the SQL Server side and how did you solve it. Hmm, sounds like a good question?

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