MICROSOFT SQL SERVER AND ORACLE COMPARED

Microsoft SQL Server and Oracle Compared

Microsoft SQL Server and Oracle Compared

Introduction

This paper contains information comparing the Microsoft SQL Server database and the Oracle database. It contains the following sections:

Schema Migration

Data Types

Data Storage Concepts

Data Manipulation Language

Discussion Analysis

Schema Migration

The schema contains the definitions of the tables, views, indexes, users, constraints, stored procedures, triggers, and other database-specific objects. Most relational databases work with similar objects. The schema migration topics discussed here include the following:

Schema Object Similarities

Schema Object Names

Table Design Considerations

Schema Object Similarities

There are many similarities between schema objects in Oracle and schema objects in Microsoft SQL Server. However, some schema objects differ between these databases, as shown in the following table:

Table 2-1 Schema Objects in Oracle and Microsoft SQL Server

Oracle

Microsoft SQL Server

Database

Database

Schema

Database and database owner (DBO)

Tablespace

Database

User

User

Role

Group/Role

Table

Table

Temporary tables

Temporary tables

Cluster

N/A

Column-level check constraint

Column-level check constraint

Column default

Column default

Unique key

Unique key or identity property for a column

Primary key

Primary key

Foreign key

Foreign key

Index

Non-unique index

PL/SQL Procedure

Transact-SQL (T-SQL) stored procedure

PL/SQL Function

T-SQL stored procedure

Packages

N/A

AFTER triggers

Triggers

BEFORE triggers

Complex rules

Triggers for each row

N/A

Synonyms

N/A

Sequences

Identity property for a column

Snapshot

N/A

View

View

Schema Object Names

Reserved words differ between Oracle and Microsoft SQL Server. Many Oracle reserved words are valid object or column names in Microsoft SQL Server. For example, DATE is a reserved word in Oracle, but it is not a reserved word in Microsoft SQL Server. Therefore, no column is allowed to have the name DATE in Oracle, but a column can be named DATE in Microsoft SQL Server. Use of reserved words as schema object names makes it impossible to use the same names across databases.

 Table Design Considerations

This section discusses the many table design issues that you need to consider when converting Microsoft SQL Server databases to Oracle. These issues are discussed under the following headings:

Data Types

Entity Integrity Constraints

Referential Integrity Constraints

Unique Key Constraints

Check Constraints

Data Types

This section describes conversion considerations for the following data types:

DATETIME Data Types

IMAGE and TEXT Data Types (Binary Large Objects)

Microsoft SQL Server User-Defined Data Types

This chapter provides detailed descriptions of the differences in data types used by Microsoft SQL Server and Oracle databases. Specifically, this chapter contains the following information:

A table showing the base Microsoft SQL Server data types available and how they are mapped to Oracle data types

Recommendations based on the information listed in the table

Data Types Table

Table 2-2 Data Types in Oracle and Microsoft SQL Server

Microsoft SQL Server

Description

Oracle

Comments

INTEGER

Four-byte integer, 31 bits, and a sign. May be abbreviated as "INT" (this abbreviation was required prior to version 5).

NUMBER(10)

It is possible to place a table constraint on columns of this type (as an option) to force values between -2^31 and2^31. Or, place appropriate constraints such as: STATE_NO between 1 and 50

SMALLINT

Two-byte integer, 15 bits, and a sign.

NUMBER(6)

It is possible to place a table constraint on columns of this type (optionally) to force values between -2^15 and 2^15. Or, place appropriate constraints such as: STATE_NO between 1 and 50

TINYINT

One byte integer, 8 bits and no sign. Holds whole numbers between 0 and 255.

NUMBER(3)

You may add a check constraint of (x between 0 and 255) where x is column name.

REAL

Floating point number. Storage is four bytes and has a binary ...