Migrating from Oracle to SQL Server
Last updated: October 3rd '02 | Best viewed with: All popular browsers | Best viewed at: 1024x768 | Links to external sites will open in a new window

About myself
My technical skills
My favorites
My picture album

Shortcut keys
My code library

VB resources
SQLServer resources
SQLServer books
Replication FAQ
Scripting resources
ASP resources

Search my site
Sign my guestbook
Contact information

SQL Server Articles New



This month's 2 click survey:
Is .NET important for a database professional?


Featured Book:
Oracle 8i and Microsoft SQL Server 2000 Integration


  NEW!!! Subscribe to my newsletter:
Want to keep in touch with the latest in SQL Server world? Email vyaskn@hotmail.com with 'subscribe' in the subject line
See also:  
Database coding conventions SQL Server interview questions
Evaluation of federated database servers SQL Server FAQ on programming, administration, replication and tools
SQL Server security best practices SQL Server administration best practices
Related books:
Inside SQL Server 2000 Beginning Oracle Programming Expert One on One: Oracle
Oracle to SQL Server Migration/Conversion Tool by AdventNet SwissSQL

Migrating from Oracle to SQL Server

In Microsoft public newsgroups, I've been observing a recent increase in the number of questions that deal with migrating from Oracle to SQL Server. It is not an easy task to migrate your Oracle database and applications to SQL Server, as there are major architectural differences between SQL Server and Oracle. Further, Oracle's PL/SQL is vastly different from Microsoft's Transact-SQL (T-SQL).

The most commonly asked question is, "Is there a tool that I can use, to translate my PL/SQL scripts, stored procedures, triggers and functions into T-SQL?" Sadly, the answer is No. There is no magic tool that can convert all your PL/SQL code to T-SQL. Even if such a tool exists, I can guarantee that it will not work in all situations. So, migrating or porting your PL/SQL code to T-SQL is a painfully manual process. The more standard (ANSI SQL) SQL you write, the more easier it is to translate, as ANSI SQL is more likely to work independant of the RDBMS. However, it need not be that manual anymore. I recently learned about a tool from DBBest.com. Do check it out! Also, do not forget to check out SwisSQL Database Migration tools.

If you are tasked with porting or migrating an Oracle database application to Microsoft SQL Server, the following are the primary resources you should approach for help and migration guidelines:

White Paper: Migrating Oracle Databases to SQL Server 2000
Microsoft SQL Server 2000 Resource Kit Read chapter 7 (Migrating Oracle Databases to SQL Server 2000) from this very useful book: Microsoft SQL Server 2000 Resource Kit

Click here to read my review of this book

Click here to buy this book from: Amazon.com or Amazon.co.uk or Amazon.ca


At the end of this article, I will provide you with more links to books, that are useful for Oracle DBAs and developers, entering the Microsoft SQL Server world.

The following table addresses the most frequently asked questions (FAQ), related to migrating from Oracle to SQL Server. This is list is by no means complete and will be updated periodically.

Oracle SQL Server More Information
%TYPE data typeNo equivalent

The %TYPE data type of Oracle, lets you create a variable and have that variable's data type be defined by a table or view column or a PL/SQL package variable.

There is no equivalent for Oracle's %TYPE datatype in T-SQL, but it can be simulated (not very conveniently though) using User Defined Data types (UDT). Here is an example:

EXEC sp_addtype 'MyType', 'smallint', NULL

CREATE TABLE MyTable (i MyType)

CREATE PROC MyProc
AS
BEGIN
DECLARE @i MyType
END

BEFORE triggersINSTEAD OF triggers

Use INSTEAD OF trigger in SQL Server as an equivalent to Oracle's BEFORE trigger.

For more information on INSTEAD OF triggers, see SQL Server Books Online

DECODE() functionCASE expression

DECODE can be conveniently simulated using the T-SQL CASE expression. Here's an example:

SELECT Sport,
CASE Sport
WHEN 'Cricket' THEN 'England'
WHEN 'Hockey' THEN 'India'
WHEN 'Base Ball' THEN 'America'
ELSE NULL
END AS 'Originating Country'
FROM Sports

DESCRIBEsp_help or sp_columns

There are a lot of alternatives for Oracle's DESCRIBE, in SQL Server. You could use the system stored procedure sp_help for detailed information about a table's columns and other properties.

If sp_help is providing you with too much information, then try the ODBC catalog stored procedure, sp_columns.

There are a bunch of other useful sp_help* stored procedures available in SQL Server. You can find more information about those in SQL Server Books Online.

If none of those procedures are suitable for your requirements, then 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.

As a last resort, you could even query system tables like sysobjects and syscolumns, but this is not a recommended approach.

DUAL tableNo equivalent

There is no DUAL table in SQL Server. In fact, you don't need one in SQL Server, as you can have a SELECT statement without a FROM clause.

For example, consider the following SELECT statement in Oracle:

SELECT 'Something'
FROM DUAL

In SQL Server, the same result can be obtained by the following command:

SELECT 'Something'

If you are porting some code from Oracle into SQL Server and if you don't want to remove all references to DUAL table, then just create a DUAL table in your database using the following commands:

CREATE TABLE DUAL
(
DUMMY varchar(1)
)

INSERT INTO DUAL (DUMMY) VALUES ('X')

INTERSECT operatorNot supported

Use EXISTS clause to generate the same result.

The following example illustrates the simulation of Oracle's INTERSECT operator:

SELECT OrderID, OrderDate
FROM Orders O
WHERE EXISTS
(
SELECT 1
FROM RefundsTable R
WHERE O.OrderID = R.OrderID
)

MINUS operatorNot supported

Use NOT EXISTS clause in your SELECT statement to generate the same result.

The following example illustrates the simulation of Oracle's MINUS operator:

SELECT OrderID, OrderDate
FROM Orders O
WHERE NOT EXISTS
(
SELECT 1
FROM RefundsTable R
WHERE O.OrderID = R.OrderID
)

Nested tablesNot supported

Oracle 8i and prior versions didn't support this feature and is introduced in Oracle 9i. This feature basically enables you to store a table, within a column. It is like having an array of records in your database columns.

SQL Server has no concept of nested tables.

As a workaround, You could store your sub-tables or child tables in the form of XML inside a char, nchar, varchar, nvarchar, text or ntext type column, and parse it as needed, at runtime. See OPENXML, sp_xml_preparedocument, sp_xml_removedocument in SQL Server 2000 Books Online.

Another possible workaround would be to store comma separated values (CSV).

Note that this is against the basic rules of normalization. Columns are nomore atomic, with nested tables.

From a design point of view, best thing to do would be, to create different tables for representing different entities and link them with primary and foreign key relationships. This will also make searching easier.

OracleSQL Server

:-)

PackagesNot supported

No equivalent in SQL Server for Oracle's Packages and Package variables concept

PL/SQLT-SQL

Every database product implements and extends the standard SQL. Oracle's implementation of SQL is called PL/SQL, while Microsoft's is called T-SQL (Transact-SQL)

Row level securityNo equivalent

Though there is no inbuilt support in SQL Server for row level permissions, you can implement it using view and system functions.

For more information and a working example, read this article:

Implementing row level permissions/security in SQL Server databases

rownum pseudo columnNo equivalent

Though there is no rownum or rowid in SQL Server, there are several ways in which a row number can be generated.

For some examples, read this article:

Q186133 INF: How to Dynamically Number Rows in a Select Statement

SELECT...FOR UPDATEUPDLOCK hint

Use the locking hint UPDLOCK in your SELECT statement.

See SQL Server Books Online for more information.

SequencesIDENTITY

It is much simpler in SQL Server to generate a sequence value for a primary key or a non-key column.

You just need to turn on the IDENTITY property of column. IDENTITY property can be set to columns of the following data types: tinyint, smallint, int, bigint, decimal, numeric

Try this example to see how it works:

CREATE TABLE foo
(
i int IDENTITY(1, 1)
j int
)

INSERT INTO foo (j) VALUES (911)
INSERT INTO foo (j) VALUES (999)

SQL *PlusQuery Analyzer

For connecting to SQL Server and executing queries and modifying data, use the built-in Query Analyzer. It is much more powerful and friendlier than Oracle's SQL *Plus

START WITH...CONNECT BY clauseNo equivalent

Though there's no direct equivalent in T-SQL for Oracle's START WITH...CONNECT BY, there are several ways and efficient techniques for processing and querying hierarcical data.

For more information, read this article:

Working with hierarchical data in SQL Server databases

SynonymViews

You can simulate Oracle Synonyms in SQL Server using Views. For example, the following creates a view that returns the OrderID and OrderDate from Orders table.

CREATE VIEW vOrders
AS
SELECT OrderID, OrderDate
FROM Orders

Now you can select data from the view, using the following SELECT statement:

SELECT * FROM vOrders

The following example will create a view which is equivalent to Oracles TABS synonym or USER_TABLES data dictionary view (For simplicity, I am only including the table name in the view definition):

CREATE VIEW TABS
AS
SELECT name AS table_name
FROM sysobjects
WHERE type = 'U'

Now you can execute the following SELECT statement to return all user table names:

SELECT table_name
FROM TABS



Suggested reading for Oracle DBAs and developers interested in moving to SQL Server:

Inside Microsoft SQL Server 2000 Inside Microsoft SQL Server 2000
By Kalen Delaney

This is THE book for DBAs interested in the architecture and internals of SQL Server

Click here to read my review

Click here to buy this book from: Amazon.com or Amazon.co.uk or Amazon.ca
Sams Teach Yourself SQL Server 2000 in 21 Days Sams Teach Yourself Microsoft SQL Server 2000 in 21 Days
By Richard Waymire, Rick Stawtell

A very well written book to quickly get you upto speed with all aspects of SQL Server

Click here to buy this book from: Amazon.com or Amazon.co.uk or Amazon.ca
Advanced Transact-SQL for SQL Server 2000 Advanced Transact-SQL for SQL Server 2000
by Itzik Ben-Gan, Tom Moreau

One of the best books written on T-SQL

Click here to buy this book from: Amazon.com or Amazon.co.uk or Amazon.ca
SQL Server 2000 Programming by Example SQL Server 2000 Programming by Example
by Fernando G. Guerrero, Carlos Eduardo Rojas

A very useful book for developers interested in learning T-SQL

Click here to buy this book from: Amazon.com or Amazon.co.uk or Amazon.ca