This whitepaper explores some tips of database migration from Oracle to MS SQL or its cloud variations such as Azure SQL.
Table Description
Oracle present table definition by using “DESC tablename” statement. In SQL Server feel free to make use of the system stored procedure sp_help for comprehensive information about a table’s columns and other properties.
If the end result is not appropriate for your requirements, then querying the system view with INFORMATION_SCHEMA.COLUMNS, will ensure you get the right information. One can also easily cover up ones code inside the stored system known as DESCRIBE, if you desire.
For the last process, you can query the system tables just like sysobjects and syscolumns, however this is not an advisable technique.
Table DUAL
When it comes to Oracle, it presents a unique one-row and one-column table that are DUAL to run a few queries that doesn’t require any table, for instance:
SELECT SYSDATE FROM DUAL;
Since MS SQL does not have the same table, it must be created during the migration for using in views and queries:
create table dual (varchar(1) not null );
insert into dual(dummy) values(‘x’);
Specific functions
Each and every specific Oracle function need to be transformed into MS SQL equivalent. However, some of them don’t have direct synonym. One of them is decode() function, it must be exchanged by CASE expression below:
SELECT id,
CASE id
WHEN ‘1’ THEN ‘one’
WHEN ‘2’ THEN ‘two’
WHEN ‘3’ THEN ‘three’
ELSE NULL
END AS ‘id_text’
FROM products
Greatest() and least() Oracle functions also do not have straight equivalent in MS SQL. These functions could be emulated in SQL Server as follows:
SELECT Greatest=MAX(col), Least=MIN(col)
FROM table_name
CROSS APPLY (
SELECT col1 UNION ALL SELECT col2 UNION ALL SELECT col3
UNION ALL SELECT col4) a(col)
GROUP BY primary_key
Triggers
The primary obstacle of transforming Oracle triggers into MS SQL format is missing particular features in the target DBMS: BEFORE INSERT/UPDATE/DELETE triggers and FOR EACH ROW pattern.
The process requires the implementation of some missing features by other method of MS SQL. For instance, semantic of BEFORE-triggers is used to modify record that are affected by the last process prior to inserting/updating it into the database. “FOR EACH ROW” structure let you implement the trigger to all rows influenced by the last insert, update or delete operation. SQL Server triggers can manage altered records in the database the moment the insert or update operation concludes. All improved records are gathered in service tables “inserted” (for insert/update operation) or “deleted” (for delete operation).
Sequences
You cannot find any support for sequences in MS SQL, the DBMS offers special attribute for numeric data types such as tinyint, smallint, int, bigint, decimal and numeric called “IDENTITY” instead:
CREATE TABLE mytable
(
id int IDENTITY(1, 1)
some_column varchar(50)
)
