Thursday, May 9, 2013

Migrating SQL Query from Oracle to SQL Server 2008 or Sybase

Oracle and Microsoft SQL Server are very different than each other and if you are migrating SQL queries or database, tables from Oracle 11g database to Microsoft 2008 SQL server than 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 sub queries 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 other 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 its most likely same job and you should first start with either SQL Server or Sybase ASE and than later migrate them from each other. In this Oracle and SQL Server tutorial we will see 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 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

Migrating SQL queries from Oracle to Microsoft SQL Server 2008As 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 sub query or derived table :
Oracle supports order by clause in sub query 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 sub query.

Error : The ORDER BY clause is invalid in views, inline functions, derived tables, sub queries, 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 sub query and derived table and move those to main result set, most of the time you can do that but if you cant than you probably need to rewrite whole query.

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

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 than 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 allow you to query derived table without specifying name but SQL Server doesn't allow. Look at following query, which works perfectly in Oracle but give 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 on other 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 form Oracle to SQL Server is related to SEQUENCE object. We were using Oracle SEQUENCE object to generate automatic incremented id and SEQUENCE is 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"). Only solution was to remove the SEQUENCE code and instead use IDENTITY feature of SQL Server, which is similar to sequence and creates automatic incremented column. You provide IDENTITY a seed and an incremental value, default is (1,1) and it automatically generates numbers for your.

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 are many 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 solved it. Hmm, sounds like good question?

No comments :

Post a Comment