How to migrate database from Oracle to SQL Server

This article explains how to convert most database objects from Oracle to SQL Server or Azure SQL.

Table Definitions

Oracle expose table definition through DESC tablename statement. In SQL Server you can use the system stored procedure sp_help for detailed information about a table's columns and other properties.

If output of this procedure is not suitable for your requirements, you could query the system view INFORMATION_SCHEMA.COLUMNS, to get the desired information. You could wrap your code inside a stored procedure named DESCRIBE, if you wish.

Finally, there are system tables like sysobjects and syscolumns that could help to extract information about table structure, however this is not a recommended approach.

Table DUAL

Oracle has special one-row and one-column table DUAL to run some queries that does not require any table, for example:

SELECT 1 FROM DUAL;

SQL Server does not have such table, so it must be created for possible use in queries:

create table dual (dummy varchar(1) not null);
insert into dual(dummy) values('x');

Specific functions

Every specific Oracle function must be converted into SQL Server equivalent according to this table:

Oracle	SQL Server

current_date, current_time, current_timestamp	getdate()
decode()	no direct equivalent, see conversion below
extract()	datepart()
greatest()	no direct equivalent, see custom code below
least()	no direct equivalent, see custom code below
sysdate, now()	gedate()
locate(), instr(), position()	charindex()
string1 \|\| string2	string1 + string2

Decode() function

This is one of Oracle functions that do not have direct equivalent in SQL Server. If must be replaced by CASE expression as follows:

SELECT colorid, 
CASE colorid
WHEN '1' THEN 'white' 
WHEN '2' THEN 'black' 
WHEN '3' THEN 'red' 
ELSE NULL 
END AS 'colorname'
FROM colors

Greatest() and least() functions

This is how greatest() and least() functions could be emulated in SQL Server:

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 main issue of converting Oracle triggers into SQL Server format is missing particular features in the target DBMS:

SQL Server does not support BEFORE INSERT/UPDATE/DELETE trigger properties
SQL Server does not support FOR EACH ROW pattern

The task is to implement missing capabilities by other means of SQL Server. For example, the goal of BEFORE-triggers is to update record affected by the last operation before inserting/updating it into the database. FOR EACH ROW pattern allows to apply the trigger to all rows affected by the last insert, update or delete operation. SQL Server triggers can process affected records in the database after insert or update operation completes. All modified records are collected in service tables "inserted" (for insert/update operation) or "deleted" (for delete operation).

Sequences

There is no support for sequences in SQL Server, the DBMS provides special property for numeric data types (tinyint, smallint, int, bigint, decimal, numeric) called "IDENTITY" instead:

CREATE TABLE mytable
(
    id int IDENTITY(1, 1)
    some_column varchar(50)
)