Distinction between Oracle SQL Question vs Microsoft SQL Server or Sybase? Reply

Oracle and Microsoft SQL Server are two of the most well-liked database however they’re very totally different from one another and in case you are migrating SQL queries or databases, tables from Oracle database to Microsoft SQL Server 2008 then you might be certain to face some points. The principle purpose for these porting points are options which might be supported and exists within the Oracle database, however not accessible in Microsoft SQL Server 2008 like SEQUENCE, Order by clause in subqueries, and derived tables, derived desk and not using a identify, and so forth. I’m certain there are just a few extra and they’re going to floor primarily based upon totally different database objects you might be utilizing in your tables and queries.


On one other hand SQL engine for SQL Server and Sybase are very a lot comparable, a minimum of syntactically, and in case you are migrating queries from SQL Server to Sybase you can do this with out a lot trouble, in fact, there shall be slight adjustments however not as a lot as Oracle.

So, in case you are migrating from Oracle to Sybase or SQL Server it is almost definitely the identical job and it’s best to first begin with both SQL Server or Sybase ASE after which later migrate them from one another. 

On this Oracle and SQL Server tutorial, we are going to see a few examples, the place Oracle and SQL Server are totally different and the way to change these SQL queries in order that they’ll run on Microsoft SQL Server. 

Oracle helps order by clause in a subquery and derived tables, however while you attempt to run the identical question, which is working superb in Oracle, in SQL Server or Sybase, you’ll get the next error as a result of they don’t assist order by clause on derived desk or subquery.

Answer:
With a purpose to clear up this drawback you want to take away order by clause from subquery and derived desk and transfer these to the principle consequence set, more often than not you are able to do that however if you cannot then you definately in all probability must rewrite the entire question. 

choose CUSTOMER_ID,CUSTOMER_ADDRESS from CUSTOMER 
the place CUSTOMER_IDS IN 
    (choose SELLER_ID from TRANSACTIONS the place ITME_TYPE='PC' 
     order by PURCHASE_DATE)

choose CUSTOMER_ID,CUSTOMER_ADDRESS from CUSTOMER 
the place CUSTOMER_IDS IN 
    (choose SELLER_ID from TRANSACTIONS the place ITME_TYPE='PC')

if this isn’t what you want then you want to rewrite this question using SQL Joins.

choose depend(*) from (
    choose BUYER_ID from TRANSACTIONS
     the place SELLER_ID= 'james_2012' 
     and item_type='PC'
    UNION
    choose SELLER_ID from TRANSACTIONS 
     the place BUYER_ID= 'james_2012' 
     and item_type='PC'
 )

choose depend(*) from (
    choose BUYER_ID from TRANSACTIONS 
    the place SELLER_ID= 'james_2012' 
    and item_type='PC'
    UNION
    choose SELLER_ID from TRANSACTIONS 
    the place BUYER_ID= 'james_2012' 
    and item_type='PC'
 ) as sells

One other drawback we confronted whereas migrating our queries from Oracle to SQL Server is said to the SEQUENCE object. We had been utilizing Oracle SEQUENCE object to geneprice routinely incremented id and SEQUENCE are usually not supported in Microsoft SQL Server (let me know if it does as a result of I attempted to create SEQUENCE and it provides the error “Unknown object kind ‘SEQUENCE’ utilized in a CREATE, DROP, or ALTER assertion”).

The one resolution was to take away the SEQUENCE code and as a substitute use the IDENTITY function of SQL Server, which is just like sequence and creates routinely incremented columns. You present IDENTITY a seed and an incremental worth, the default is (1,1) and it routinely generates numbers for yours. 

What involves us as a nice shock was pagination queries, we had some oracle pagination queries in our software which is written utilizing the row_num() perform and we’re fascinated with the way to migrate them into the SQL server, earlier than discovering that SQL Server additionally helps ROW_NUM() perform.

That is it on migrating SQL queries from Oracle to SQL Server or Sybase ASE database. I’m certain there’s way more distinction between Oracle and SQL Server, which I have not coated just because I have not confronted them.

By the way in which, these had been a number of the most typical adjustments, you want to make to run your oracle question into the SQL server.  In case you have additionally finished the identical job then please tell us what sort of subject you will have confronted on the SQL Server aspect and the way did you clear up it. Hmm, feels like query?