This chapter contains information comparing the Microsoft SQL
Server database and the Oracle database. It contains the following sections:
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:
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:
|
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
|
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.
You
should choose a schema object name that is unique by case and by at least one
other characteristic, and ensure that the object name is not a reserved word
from either database.
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
- DATETIME Data Types
- IMAGE and TEXT Data Types (Binary Large
Objects)
- Microsoft SQL Server User-Defined Data
Types
The
date/time precision in Microsoft SQL Server is 1/300th of a second. Oracle has
the data type TIMESTAMP which has a precision of 1/100000000th of a second.
Oracle also has a DATE data type that stores date and time values accurate to
one second. SQL Developer has a default mapping to the DATE data type.
For
applications that require finer date/time precision than seconds, the TIMESTAMP
data type should be selected for the data type mapping of date data types in
Microsoft SQL Server. The databases store point-in-time values for DATE and
TIME data types.
As
an alternative, if a Microsoft SQL Server application uses the DATETIME column
to provide unique IDs instead of point-in-time values, replace the DATETIME
column with a SEQUENCE in the Oracle schema definition.
In
the following examples, the original design does not allow the DATETIME
precision to exceed seconds in the Oracle table. This example assumes that the
DATETIME column is used to provide unique IDs. If millisecond precision is not
required, the table design outlined in the following example is sufficient:
Original Table Design
Microsoft SQL Server:
CREATE TABLE
example_table
(datetime_column datetime not null,
text_column text null,
varchar_column varchar(10) null)
Oracle:
CREATE TABLE
example_table
(datetime_column date not null,
text_column long null,
varchar_column varchar2(10) null)
The
following design allows the value of the sequence to be inserted into the
integer_column. This allows you to order the rows in the table beyond the
allowed precision of one second for DATE data type fields in Oracle. If you
include this column in the Microsoft SQL Server table, you can keep the same
table design for the Oracle database.
Revised
Table Design
Microsoft SQL Server:
CREATE TABLE
example_table
(datetime_column datetime not null,
integer_column int null,
text_column text null,
varchar_column varchar(10) null)
Oracle:
CREATE TABLE
example_table
(datetime_column date not null,
integer_column number null,
text_column long null,
varchar_column varchar2(10) null)
For
the Microsoft SQL Server database, the value in the integer_column is always
NULL. For Oracle, the value for the field integer_column is updated with the
next value of the sequence.
Create
the sequence by issuing the following command:
CREATE SEQUENCE
datetime_seq
Values
generated for this sequence start at 1 and are incremented by 1.
Many
applications do not use DATETIME values as UNIQUE IDs, but still require the
date/time precision to be higher than secondS. For example, the timestamp of a
scientific application may have to be expressed in milliseconds, microseconds,
and nanoseconds. The precision of the Microsoft SQL Server DATETIME data type
is 1/300th of a second; the precision of the Oracle DATE data type is one
second. The Oracle TIMESTAMP data type has a precision to 1/100000000th of a
second. However, the precision recorded is dependent on the operating system.
The
physical and logical storage methods for IMAGE and TEXT data differ from
Oracle to Microsoft SQL Server. In Microsoft SQL Server, a pointer to the IMAGE or TEXT data is stored with the rows in
the table while the IMAGE or TEXT data is stored separately. This
arrangement allows multiple columns of IMAGE or TEXT data per table. In
Oracle, IMAGE data may be stored in a BLOB type field and TEXT data may be stored in a CLOB type field. Oracle allows multiple BLOB and CLOB columns per table. BLOBS and CLOBS may or may not be
stored in the row depending on their size.
If
the Microsoft SQL Server TEXT column is such
that the data never exceeds 4000 bytes, convert the column to an Oracle
VARCHAR2 data type column instead of a CLOB column. An Oracle table can define multiple VARCHAR2 columns. This size of TEXT data is suitable for most applications.
This
Microsoft SQL Server T-SQL-specific enhancement to SQL allows users to define
and name their own data types to supplement the system data types. A
user-defined data type can be used as the data type for any column in the
database. Defaults and rules (check constraints) can be bound to these
user-defined data types, which are applied automatically to the individual
columns of these user-defined data types.
When
migrating to Oracle PL/SQL, the Migration Workbench determines the base data
type for each user-defined data type, and it finds the equivalent PL/SQL data
type.
Note:
User-defined data types make the data definition
language code and procedural SQL code less portable across different database
servers.
You
can define a primary key for a table in Microsoft SQL Server. Primary keys can
be defined in a CREATE TABLE statement
or an ALTER
TABLE statement.
Oracle
provides declarative referential integrity. A primary key can be defined as
part of a CREATE
TABLE or an ALTER TABLE statement. Oracle internally creates a
unique index to enforce the integrity.
You
can define a foreign key for a table in Microsoft SQL Server. Foreign keys can
be defined in a CREATE TABLE statement
or an ALTER
TABLE statement.
Oracle
provides declarative referential integrity. A CREATE TABLE or ALTER TABLE statement can add foreign keys to the table definition. For
information about referential integrity constraints, see Oracle Database Concepts.
You
can define a unique key for a table in Microsoft SQL Server. Unique keys can be
defined in a CREATE TABLE statement or an ALTER TABLE statement.
Oracle
defines unique keys as part of CREATE TABLE or ALTER TABLE statements. Oracle
internally creates unique indexes to enforce these constraints.
Unique
keys map one-to-one from Microsoft SQL Server to Oracle.
Check
constraints can be defined in a CREATE TABLE statement or an ALTER TABLE
statement in Microsoft SQL Server. Multiple check constraints can be defined on
a table. A table-level check constraint can reference
any column in the constrained table. A column can have only one check
constraint. A column-level check constraint can
reference only the constrained column. These check constraints support complex
regular expressions.
Oracle
defines check constraints as part of the CREATE TABLE or ALTER TABLE
statements. A check constraint is defined at the TABLE level and not at the
COLUMN level. Therefore, it can reference any column in the table. Oracle,
however, does not support complex regular expressions.
create rule phone_rule
as
@phone_number like
"([0-9][0-9][0-9])[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]"
This
rule passes all the phone numbers that resemble the following: (650)506-7000
This
rule fails all the phone numbers that resemble the following:
650-506-7000
650-GET-HELP
There
are a few ways to implement this INTEGRITY constraint in Oracle:
- Simulate the behavior of
phone-rule in a check constraint using a combination of SUBSTR, TRANSLATE,
and LIKE clauses
- Write a trigger and use PL/SQL
Table-level
check constraints from Microsoft SQL Server databases map one-to-one with
Oracle check constraints. You can implement the column-level check constraints
from the Microsoft SQL Server database to Oracle table-level check constraints.
While converting the regular expressions, convert all simple regular
expressions to check constraints in Oracle. Microsoft SQL Server check
constraints with complex regular expressions can be either reworked as check
constraints including a combination of simple regular expressions, or you can
write Oracle database triggers to achieve the same functionality.
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
|
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 precision of 24 bits, a 7-digit
precision.
Data can range from
–3.40E+38 to 3.40E+38.
|
FLOAT
|
The ANSI data type
conversion to Oracle for REAL is FLOAT(63). By default, the Oracle Migration
Workbench maps REAL to FLOAT(24) that stores up to 8 significant decimal
digits in Oracle.
The Oracle NUMBER data
type is used to store both fixed and floating-point numbers in a format that
is compatible with decimal arithmetic. You may want to add a check constraint
to constrain range of values. Also, you get different answers when performing
operations on this data type as the Oracle NUMBER type is more precise and
portable than REAL. Floating-point numbers can be specified in Oracle in the
following format: FLOAT[(b)]. Where [(b)] is the binary precision b and can
range from 1 to 126. [(b)] defaults to 126. To check what a particular binary
precision is in terms of decimal precision, multiply [(b)] by 0.30103 and
round up to the next whole number.
|
|
A floating point
number. This column has 15-digit precision.
|
FLOAT
|
The ANSI data type
conversion to Oracle for FLOAT(p) is FLOAT(p). The ANSI data type conversion
to Oracle for DOUBLE PRECISION is FLOAT(126). By default, the Oracle
Migration Workbench maps FLOAT to FLOAT(53), that stores up to 16 significant
decimal digits in Oracle.
The Oracle NUMBER data
type is used to store both fixed and floating-point numbers in a format
compatible with decimal arithmetic.You get different answers when performing
operations on this type due to the fact that the Oracle NUMBER type is much
more precise and portable than FLOAT, but it does not have the same range.
The NUMBER data type data can range from -9.99.99E+125 to 9.99.99E+125 (38
nines followed by 88 zeros).
NOTE: If you try to
migrate floating point data greater than or equal to 1.0E+126 then Migration
Workbench will fail to insert this data in the Oracle database and1 will
return an error.This also applies to negative values less than or equal to
-1.0E+126.
|
|
|
Floating-point numbers
can be specified in Oracle using FLOAT[(b)], where [(b)] is the binary
precision [(b)] and can range from 1 to 126. [(b)] defaults to 126.To check
what a particular binary precision is in terms of decimal precision multiply
[(b)] by 0.30103 and round up to the next whole number.
If they are outside of
the range, large floating-point numbers will overflow, and small floating-point
numbers will underflow.
|
|||
|
BIT
|
A Boolean 0 or 1
stored as one bit of a byte. Up to 8-bit columns from a table may be stored
in a single byte, even if not contiguous. Bit data cannot be NULL, except for
Microsoft SQL Server 7.0, where null is allowed by the BIT data type.
|
NUMBER(1)
|
In Oracle, a bit is
stored in a number(1) (or char). In Oracle, it is possible to store bits in a
char or varchar field (packed) and supply PL/SQL functions to set / unset /
retrieve / query on them.
|
|
Fixed-length string of
exactly n 8-bit characters, blank padded. Synonym for CHARACTER. 0 < n
< 256 for Microsoft SQL Server. 0 < n < 8000 for Microsoft SQL
Server 7.0.
|
CHAR(n)
|
Pro*C client programs
must use mode=ansi to have characters interpreted correctly for string
comparison, mode=oracle otherwise.
A CHAR data type with
a range of 2001 to 4000 is invalid. SQL Developer automatically converts a
CHAR datatype with this range to VARCHAR2.
|
|
|
Varying-length
character string. 0 < n < 256 for Microsoft SQL Server. 0 < n <
8000 for Microsoft SQL Server 7.0.
|
VARCHAR2(n)
|
|
|
|
TEXT
|
Character string of
8-bit bytes allocated in increments of 2k pages. "TEXT" is stored
as a linked-list of 2024-byte pages, blank padded. TEXT columns can hold up
to (231-1) characters.
|
CLOB
|
The CLOB field can
hold up to 4GB.
|
|
IMAGE
|
Binary string of 8-bit
bytes. Holds up to (231-1) bytes of binary data.
|
BLOB
|
The BLOB field can
hold up to 4GB.
|
|
BINARY(n)
|
Fixed length binary
string of exactly n 8-bit bytes. 0 < n < 256 for Microsoft SQL Server.
0 < n < 8000 for Microsoft SQL Server 7.0.
|
RAW(n)/BLOB
|
|
|
VARBINARY(n)
|
Varying length binary
string of up to n 8-bit bytes. 0 < n < 256 for Microsoft SQL Server. 0
< n < 8000 for Microsoft SQL Server 7.0.
|
RAW(n)/BLOB
|
|
|
Date and time are
stored as two 4-byte integers. The date portion is represented as a count of
the number of days offset from a baseline date (1/1/1900) and is stored in
the first integer. Permitted values are legal dates between 1st January, 1753
AD and 31st December, 9999 AD. Permitted values in the time portion are legal
times in the range 0 to 25920000. Accuracy is to the nearest 3.33
milliseconds with rounding downward. Columns of type DATETIME have a default
value of 1/1/1900.
|
DATE
|
The precision of DATE
in Oracle and DATETIME in Microsoft SQL Server is different. The DATETIME
data type has higher precision than the DATE data type. This may have some
implications if the DATETIME column is supposed to be UNIQUE. In Microsoft
SQL Server, the column of type DATETIME can contain UNIQUE values because the
DATETIME precision in Microsoft SQL Server is to the hundredth of a second.
In Oracle, however, these values may not be UNIQUE as the date precision is
to the second. You can replace a DATETIME column with two columns, one with
data type DATE and another with a sequence, in order to get the UNIQUE
combination. It is preferable to store hundredths of seconds in the second
column.
The Oracle TIMESTAMP
data type can also be used. It has a precision of 1/10000000th of a second.
|
|
|
SMALL-DATETIME
|
Date and time stored
as two 2-byte integers. Date ranges from 1/1/1900 to 6/6/2079. Time is the
count of the number of minutes since midnight.
|
DATE
|
With optional check
constraint to validate the smaller range.
|
|
MONEY
|
A monetary value
represented as an integer portion and a decimal fraction, and stored as two
4-byte integers. Accuracy is to the nearest 1/10,000. When inputting Data of
this type it should be preceded by a dollar sign ($). In the absence of the
"$" sign, Microsoft SQL Server creates the value as a float.
Monetary data values
can range from -922,337,203,685,477.5808 to 922,337,203,685,477.5807, with
accuracy to a ten-thousandth of a monetary unit. Storage size is 8 bytes.
|
NUMBER(19,4)
|
Microsoft SQL Server
inputs MONEY data types as a numeric data type with a preceding dollar sign
($) as in the following example, select * from table_x where y > $5.00 You
must remove the "$" sign from queries. Oracle is more general and
works in international environments where the use of the "$" sign
cannot be assumed. Support for other currency symbols and ISO standards
through NLS is available in Oracle.
|
|
NCHAR(n)
|
Fixed-length character
data type which uses the UNICODE UCS-2 character set. n must be a value in the
range 1 to 4000. SQL Server storage size is two times n.
Note: Microsoft SQL
Server storage size is two times n. The Oracle Migration Workbench maps
columns sizes using byte semantics, and the size of Microsoft SQL Server
NCHAR data types appear in the Oracle Migration Workbench Source Model with
"Size" specifying the number of bytes, as opposed to the number of
Unicode characters. Thus, a SQL Server column NCHAR(1000) will appear in the
Source Model as NCHAR(2000).
|
CHAR(n*2)
|
|
|
NVARCHAR(n)
|
Fixed-length character
data type which uses the UNICODE UCS-2 character set. n must be a value in
the range 1 to 4000. SQL Server storage size is two times n.
Note: Microsoft SQL
Server storage size is two times n. The Oracle Migration Workbench maps
columns sizes using byte semantics, and the size of Microsoft SQL Server
NVARCHAR data types appear in the Oracle Migration Workbench Source Model
with "Size" specifying the number of bytes, as opposed to the
number of Unicode characters. Thus, a SQL Server column NVARCHAR(1000) will
appear in the Source Model as NVARCHAR(2000).
|
VARCHAR(n*2)
|
|
|
SMALLMONEY
|
Same as MONEY except
monetary data values from -214,748.3648 to +214,748.3647, with accuracy to
one ten-thousandth of a monetary unit. Storage size is 4 bytes.
|
NUMBER(10,4)
|
Since the range is
-214,748.3648 to 214,748.364, NUMBER(10,4) suffices for this field.
|
|
TIMESTAMP is defined
as VARBINARY(8) with NULL allowed. Every time a row containing a TIMESTAMP
column is updated or inserted, the TIMESTAMP column is automatically
increment by the system. A TIMESTAMP column may not be updated by users.
|
NUMBER
|
You must place
triggers on columns of this type to maintain them. In Oracle you can have
multiple triggers of the same type without having to integrate them all into
one big trigger. You may want to supply triggers to prevent updates of this
column to enforce full compatibility.
|
|
|
VARCHAR(30) in
Microsoft SQL Server.
NVARCHAR(128) in
Microsoft SQL Server 7.0.
|
VARCHAR2(30) and
VARCHAR2(128) respectively.
|
|
TEXT
and IMAGE data types in Microsoft SQL Server follow these rules:
- The column of these data types
cannot be indexed.
- The column cannot be a primary
key.
- The column cannot be used in
the GROUP BY, ORDER BY, HAVING, and DISTINCT clauses.
- IMAGE and TEXT data types can
be referred to in the WHERE clause with the LIKE construct.
- IMAGE and TEXT data types can
also be used with the SUBSTR and LENGTH functions.
In
Microsoft SQL Server, only columns with variable-length data types can store
NULL values. When you create a column that allows NULLs with a fixed-length
data type, the column is automatically converted to a system variable-length
data type, as illustrated in Table 2-3. These variable-length data types are reserved system data types,
and users cannot use them to create columns
|
Fixed-Length Data
Type
|
Variable-Length
Data Type
|
|
CHAR
|
VARCHAR
|
|
NCHAR
|
NVARCHAR
|
|
BINARY
|
VARBINARY
|
|
DATETIME,
SMALLDATETIME
|
DATETIMN
|
|
FLOAT
|
FLOATN
|
|
INT, SMALLINT, TINYINT
|
INTN
|
|
DECIMAL
|
DECIMALN
|
|
NUMERIC
|
NUMERICN
|
|
MONEY, SMALLMONEY
|
MONEYN
|
Note:
The Oracle Migration Workbench Source Model will
display table system data types for each column.
Recommendations
In
addition to the data types listed in Table 2-2, users can define their own data
types in Microsoft SQL Server databases. These user-defined data types
translate to the base data types that are provided by the server. They do not allow
users to store additional types of data, but can be useful in implementing
standard data types for an entire application.
You
can map data types from Microsoft SQL Server to Oracle with the equivalent data
types listed in Table 2-3. SQL Developer converts user-defined data types to their base
type. You can defined how the base type is mapped to an Oracle type in the Data
Type Mappings page in the Options dialog.
This
section provides a detailed description of the conceptual differences in data
storage for the Microsoft SQL Server and Oracle databases.
Specifically,
it contains the following information:
- A table (Table 2-4) comparing the data storage concepts of Microsoft SQL
Server, and Oracle databases
- Recommendations based on the
information listed in the table
|
Microsoft SQL
Server
|
Oracle
|
|
A database device is
mapped to the specified physical disk files.
|
One or more data files
are created for each tablespace to physically store the data of all logical
structures in a tablespace. The combined size of the data files in a
tablespace is the total storage capacity of the tablespace. The combined
storage capacity of a the tablespaces in a database is the total storage
capacity of the database. Once created, a data file cannot change in size.
This limitation does not exist in Oracle.
|
|
Many pages constitute
a database device. Each page contains a certain number of bytes.
|
One data block
corresponds to a specific number of bytes, of physical database space, on the
disk. The size of the data block can be specified when creating the database.
A database uses and allocates free database space in Oracle data blocks.
|
|
Eight pages make one
extent. Space is allocated to all the databases in increments of one extent
at a time.
|
Extent:
An extent is a
specific number of contiguous data blocks, obtained in a single allocation.
|
|
N/A
|
Segments:
A segment is a set of
extents allocated for a certain logical structure. The extents of a segment
may or may not be contiguous on disk, and may or may not span the data files.
|
|
Segments (corresponds
to Oracle Tablespace):
A segment is the name
given to one or more database devices. Segment names are used in CREATE TABLE
and CREATE INDEX constructs to place these objects on specific database
devices. Segments can be extended to include additional devices as and when
needed by using the SP_EXTENDSEGMENT system procedure.
The following segments
are created along with the database:
Segments are subsets
of database devices.
|
A database is divided
into logical storage units called tablespaces. A tablespace is used to group
related logical structures together. A database typically has one system
tablespace and one or more user tablespaces.
Tablespace Extent:
An extent is a
specific number of contiguous data blocks within the same tablespace.
Tablespace Segments:
A segment is a set of
extents allocated for a certain logical database object. All the segments assigned
to one object must be in the same tablespace. The segments get the extents
allocated to them as and when needed.
There are four
different types of segments as follows:
|
|
Tablespace Segments
(Cont):
|
|
|
|
|
Log Devices:
These are logical
devices assigned to store the log. The database device to store the logs can
be specified while creating the database.
|
Each database has a
set of two or more redo log files. All changes made to the database are
recorded in the redo log. Redo log files are critical in protecting a
database against failures. Oracle allows mirrored redo log files so that two
or more copies of these files can be maintained. This protects the redo log
files against failure of the hardware the log file reside on.
|
|
Database Devices:
A database device
contains the database objects. A logical device does not necessarily refer to
any particular physical disk or file in the file system.
The database and logs
are stored on database devices. Each database device must be initialized
before being used for database storage. Initialization of the database device
initializes the device for storage and registers the device with the server.
After initialization, the device can be:
The SP_HELPDEVICES
system procedure displays all the devices that are registered with the
server. Use the DROP DEVICE DEVICE_NAME command to drop the device. The
system administrator (SA) should restart the server after dropping the
device.
A device can be
labeled as a default device so that the new databases need not specify the
device at the time of creation. Use the SP_DISKDEFAULT system procedure to
label the device as a default device.
|
N/A
|
|
Dump Devices
These are logical
devices. A database dump is stored on these devices. The DUMP DATABASE
command uses the dump device to dump the database.
|
N/A
|
|
N/A
|
Each database has a
control file. This file records the physical structure of the database. It
contains the following information:
It is possible to have
mirrored control files. Each time an instance of an Oracle database is
started, its control file is used to identify the database, the physical
structure of the data, and the redo log files that must be opened for the
database operation to proceed. The control file is also used for recovery if
necessary. The control files hold information similar to the master database
in Microsoft SQL Server.
|
Recommendations:
The
conceptual differences in the storage structures do not affect the conversion
process directly. However, the physical storage structures need to be in place
before conversion of the database begins.
Oracle
and Microsoft SQL Server have a way to control the physical placement of a
database object. In Microsoft SQL Server, you use the ON SEGMENT clause and in
Oracle you use the TABLESPACE clause.
An
attempt should be made to preserve as much of the storage information as
possible when converting from Microsoft SQL Server to Oracle. The decisions
that were made when defining the storage of the database objects for Microsoft
SQL Server should also apply for Oracle. Especially important are initial
object sizes and physical object placement.
This section uses tables to compare the syntax
and description of Data Manipulation Language (DML) elements in the Microsoft
SQL Server and Oracle databases. Each table is followed by a recommendations
section based on the information in the tables. The following topics are
presented in this section:
- Connecting to the Database
- SELECT Statement
- SELECT with GROUP BY Statement
- INSERT Statement
- UPDATE Statement
- DELETE Statement
- Operators
- Built-In Functions
- Locking Concepts and Data Concurrency
Issues
- Logical Transaction Handling
|
Microsoft SQL
Server
|
Oracle
|
|
Syntax:
USE database_name
|
Syntax:
CONNECT user_name/password
SET
role
|
|
Description:
A default database is
assigned to each user. This database is made current when the user logs on to
the server. A user executes the USE DATABASE_NAME command to switch to
another database.
|
|
Recommendations:
This
concept of connecting to a database is conceptually different in the Microsoft
SQL Server and Oracle databases. A Microsoft SQL Server user can log on to the
server and switch to another database residing on the server, provided the user
has privileges to access that database. An Oracle Server controls only one
database, so here the concept of a user switching databases on a server does
not exist. Instead, in Oracle a user executes the SET ROLE command to change
roles or re-issues a CONNECT command using a different user_name.
|
Microsoft SQL
Server
|
Oracle
|
|
Syntax:
SELECT [ALL | DISTINCT] {select_list}
[INTO
[owner.]table]
[FROM
[owner.]{table | view}[alias] [HOLDLOCK]
[,[owner.]{table | view }[alias]
[HOLDLOCK]]...]
[WHERE
condition]
[GROUP
BY [ALL] aggregate_free_expression [, aggregate_free_expression]...]
[HAVING
search_condition]
[UNION
[ALL] SELECT...]
[ORDER
BY {[[owner.]{table | view }.]column | select_list_number | expression}
[ASC |
DESC]
[,{[[owner.]{table | view }.]column |
select_list_number | expression}
[ASC |
DESC]...]
[COMPUTE row_aggregate(column)
[,row_aggregate(column)...]
[BY
column [, column...]]]
[FOR
BROWSE]
The
individual element in the select list is as follows:
[alias
= ]
{* |
[owner.]{table | view}.* | SELECT ... | {[owner.]table.column |
constant_literal | expression}
[alias]}
|
Syntax:
SELECT [ALL | DISTINCT] {select_list}
FROM [user.]{table | view } [@dblink] [alias]
[, [user.] {table | view3} [@dblink]
[alias]...
[WHERE condition]
[CONNECT BY condition [START WITH condition]]
[GROUP BY aggregate_free_expression
[,aggregate_free_expression]...]
[HAVING search_condition]
[ {UNION [ALL] | INTERSECT | MINUS} SELECT
...]
[ORDER BY {expression | position} [ASC |
DESC]...]
[FOR UPDATE [OF [[user.]{table | view}.]column
[,[[user.]{table | view}.]column... ]
[noWAIT] ]
The individual element in the select list is
as follows:
{ * | [owner.]{table | view | snapshot |
synonym}.* | {[owner.]table.column | constant_literal | expression }
alias]}
|
|
Description:
DISTINCT eliminates
the duplicate rows.
The INTO clause and
the items that follow it in the command syntax are optional, because
Microsoft SQL Server allows SELECT statements without FROM clauses as can be
seen in the following example:
SELECT getdate()
SELECT...INTO allows
you to insert the results of the SELECT statement into a table.
SELECT_LIST can
contain a SELECT statement in the place of a column specification as follows:
SELECT d.empno, d.deptname,
empname = (SELECT ename FROM emp
WHERE enum = d.empno)
FROM dept d
WHERE deptid = 10
The preceding example
also shows the format for the column alias.
ALIAS = selected_column
COMPUTE attaches
computed values at the end of the query. These are called row_aggregates.
If a GROUP BY clause
is used, all non-aggregate select columns are needed.
FOR BROWSE keywords
are used to get into browse mode. This mode supports the ability to perform
updates while viewing data in an OLTP environment. It is used in front-end
applications using DB-Library and a host programming language. Data
consistency is maintained using the TIMESTAMP field in a multi-user
environment. The selected rows are not locked; other users can view the same
rows during the transaction. A user can update a row if the TIMESTAMP for the
row is unchanged since the time of selection.
|
Description:
DISTINCT eliminates
the duplicate rows.
The INSERT INTO
<table> SELECT FROM.... construct allows you to insert the results of
the SELECT statement into a table.
COLUMN ALIAS is
defined by putting the alias directly after the selected COLUMN.
If you use TABLE
ALIAS, the TABLE must always be referenced using the ALIAS.
You can also retrieve
data from SYNONYMS.
EXPRESSION could be a
column name, a literal, a mathematical computation, a function, several
functions combined, or one of several PSEUDO-COLUMNS.
If a GROUP BY clause
is used, all non-aggregate select columns must be in a GROUP BY clause.
The FOR UPDATE clause
locks the rows selected by the query. Other users cannot lock these row until
you end the transaction. This clause is not a direct equivalent of the FOR
BROWSE mode in Microsoft SQL Server.
|
Microsoft
SQL Server supports SELECT statements that do not have a FROM clause. This can
be seen in the following example
SELECT getdate()
Oracle
does not support SELECTs without FROM clauses. However, Oracle provides the
DUAL table which always contains one row. Use the DUAL table to convert
constructs such as the preceding one.
Translate
the preceding query to:
SELECT sysdate FROM
dual;
The
Microsoft SQL Server SELECT INTO statement can insert rows into a table. This
construct, which is part SELECT and part INSERT, is not supported by ANSI. Replace
these statements with INSERT...SELECT statements in Oracle.
If
the Microsoft SQL Server construct is similar to the following:
SELECT col1, col2, col3
INTO target_table
FROM source_table
WHERE where_clause
you
should convert it to the following for Oracle:
INSERT into target_table
SELECT col1, col2, col3
FROM source_table
WHERE where_clause
Convert
column aliases from the following Microsoft SQL Server syntax:
SELECT employees=col1
FROM tab1e
to
the following Oracle syntax:
SELECT col1 employees
FROM tab1e
Note:
Microsoft SQL Server also supports Oracle-style
column aliases.
Remove
table aliases (also known as correlation names) unless they are used
everywhere.
Replace
the COMPUTE clause with another SELECT. Attach the two sets of results using the UNION clause.
Table 2-7 compares the SELECT with GROUP BY statement in Oracle to the same statement
in Microsoft SQL Server.
|
Microsoft SQL
Server/Server
|
Oracle
|
|
Syntax:
See the SELECT
Statement section.
|
Syntax:
See the SELECT
Statement section.
|
|
Description:
Non-aggregate SELECT
columns must be in a GROUP BY clause.
|
Description:
All non-aggregate
SELECT columns must be in a GROUP BY clause.
|
The
statements illustrated in the following table add one or more rows to the table
or view.
|
Microsoft SQL
Server
|
Oracle
|
|
Syntax:
INSERT [INTO] [[database.]owner.] {table |
view}[(column [, column]...)]{VALUES (expression [,expression]...) | query}
|
Syntax:
INSERT INTO [user.]{table |
view}[@dblink][(column [, column]...)]{VALUES (expression [, expression]...)
| query...};
|
|
Description:
INTO is optional.
Inserts are allowed in
a view provided only one of the base tables is undergoing change.
|
Description:
INTO is required.
Inserts can only be
done on single table views.
|
Recommendations:
INSERT
statements in Microsoft SQL Server must be changed to include an INTO clause if
it is not specified in the original statement.
The
values supplied in the VALUES clause in either database may contain functions.
The Microsoft SQL Server-specific functions must be replaced with the
equivalent Oracle constructs.
Note:
Oracle lets you create functions that directly
match most Microsoft SQL Server functions.
Convert
inserts that are inserting into multi-table views in Microsoft SQL Server to
insert directly into the underlying tables in Oracle.
The
statement illustrated in Table 2-9 updates the data in a table or the data in a table
referenced by a view.
|
Microsoft SQL
Server
|
Oracle
|
|
Syntax:
UPDATE [[database.]owner.] {table | view}
SET [[[database.]owner.] {table. | view.}]
column = expression | NULL |
(select_statement)
[, column = expression | NULL |
(select_statement)]...
[FROM [[database.]owner.]table | view
[, [[database.]owner.]table | view]...
[WHERE condition]
|
Syntax:
UPDATE [user.]{table | view} [@dblink]
SET [[ user.] {table. | view.}]
{ column = expression | NULL |
(select_statement)
[, column = expression | NULL |
(select_statement)...] |
(column [, column]...) = (select_statement)}
[WHERE {condition | CURRENT OF cursor}]
|
|
Description:
The FROM clause is
used to get the data from one or more tables into the table that is being
updated or to qualify the rows that are being updated.
Updates through
multi-table views can modify only columns in one of the underlying tables.
|
Description:
A single subquery may
be used to update a set of columns. This subquery must select the same number
of columns (with compatible data types) as are used in the list of columns in
the SET clause.
The CURRENT OF cursor clause causes the UPDATE statement to
effect only the single row currently in the cursor as a result of the last
FETCH. The cursor SELECT statement must have included in the FOR UPDATE
clause.
Updates can only be
done on single table views.
|
Recommendations:
There
are two ways to convert UPDATE statements with FROM clauses, as indicated in
the following sections.
Use
the subquery in the SET clause if columns are being updated to values coming
from a different table.
Convert
the following in Microsoft SQL Server:
update titles
SET pub_id =
publishers.pub_id
FROM titles, publishers
WHERE titles.title LIKE
'C%'
AND publishers.pub_name
= 'new age'
to
the following in Oracle:
UPDATE titles
SET pub_id =
( SELECT a.pub_id
FROM publishers a
WHERE publishers.pub_name = 'new age'
)
WHERE titles.title like
'C%'
Use
the subquery in the WHERE clause for all other UPDATE...FROM statements.
Convert
the following in Microsoft SQL Server:
UPDATE shipping_parts
SET qty = 0
FROM shipping_parts sp,
suppliers s
WHERE sp.supplier_num =
s.supplier_num
AND s.location = "USA"
to
the following in Oracle:
UPDATE shipping_parts
SET qty = 0
WHERE supplier_num IN (
SELECT supplier_num
FROM suppliers WHERE
location = 'USA')
The
statement illustrated in Table 2-10 removes rows from tables and rows from tables referenced in
views.
|
Microsoft SQL
Server
|
Oracle
|
|
Syntax:
DELETE [FROM] [[database.]owner.]{table |
view}
[FROM [[database.]owner.]{table | view}
[, [[database.]owner.]{table | view}]...]
[WHERE where_clause]
|
Syntax:
DELETE [FROM] [user.]{table | view} [@dblink]
[alias]
[WHERE where_clause]
|
|
Description:
The first FROM in
DELETE FROM is optional.
The second FROM clause
is a Microsoft SQL Server extension that allows the user to make deletions
based on the data in other tables. A subquery in the WHERE clause serves the same
purpose.
Deletes can only be
performed through single table views.
|
Description:
FROM is optional.
ALIAS can be specified
for the table name as a correlation name, which can be used in the condition.
Deletes can only be
performed through single table views
|
Remove
the second FROM clause from the DELETE statements.
Convert
the following Microsoft SQL Server query:
DELETE
FROM sales
FROM sales, titles
WHERE sales.title_id =
titles.title_id
AND titles.type =
'business'
to
the following in Oracle:
DELETE
FROM sales
WHERE title_id in
( SELECT title_id
FROM titles
WHERE type = 'business'
)
Remove
the second FROM even if the WHERE contains a multi-column JOIN.
Convert
the following Microsoft SQL Server query:
DELETE
FROM sales
FROM sales, table_x
WHERE sales.a =
table_x.a
AND sales.b = table_x.b
AND table_x.c = 'd'
to
the following in Oracle:
DELETE
FROM sales
WHERE ( a, b ) in
( SELECT a, b
FROM table_x
WHERE c = 'd' )
This
section compares the different operators used in the Microsoft SQL Server and
Oracle databases. It includes the following subsections:
Table 2-11 compares the operators used in the Microsoft SQL Serve, and
Oracle databases. Comparison operators are used in WHERE clauses and COLUMN
check constraints or rules to compare values.
|
Operator
|
Same in Both
Databases
|
Microsoft SQL
Server Only
|
Oracle Only
|
|
Equal to
|
=
|
||
|
Not equal to
|
!=
<>
|
^=
|
|
|
Less than
|
<
|
|
|
|
Greater than
|
>
|
||
|
Less than or equal to
|
<=
|
!>
|
|
|
Greater than or equal
to
|
>=
|
!<
|
|
|
Greater than or equal
to x and less than or
equal to y
|
BETWEEN x AND y
|
||
|
Less than x or greater than y
|
NOT BETWEEN x AND y
|
||
|
Pattern Matches
a followed by 0 or
more characters
a followed by
exactly 1 character
a followed by any
character between x and z
a followed by any
character except those between x and z
a followed
by %
|
LIKE 'a%'
LIKE 'a_'
|
LIKE'a[x-z]'
LIKE'a[^x-z]'
|
LIKE 'a\%'
ESCAPE '\'
|
|
Does not match pattern
|
NOT LIKE
|
||
|
No value exists
|
IS NULL
|
||
|
A value exists
|
IS NOT NULL
|
||
|
At least one row
returned by query
|
EXISTS (query)
|
||
|
No rows returned by
query
|
NOT EXISTS (query)
|
||
|
Equal to a member of
set
|
IN =ANY
|
= SOME
|
|
|
Not equal to a member
of set
|
NOT IN != ANY <>
ANY
|
!= SOME <> SOME
|
|
|
Less than a member of
set
|
< ANY
|
< SOME
|
|
|
Greater than a member
of set
|
> ANY
|
> SOME
|
|
|
Less than or equal to
a member of set
|
<= ANY
|
!> ANY
|
<= SOME
|
|
Greater than or equal
to a member of set
|
>= ANY
|
!< ANY
|
>= SOME
|
|
Equal to every member
of set
|
=ALL
|
||
|
Not equal to every
member of set
|
!= ALL <> ALL
|
||
|
Less than every member
of set
|
< ALL
|
||
|
Greater than every
member of set
|
> ALL
|
||
|
Less than or equal to
every member of set
|
<= ALL
|
!> ALL
|
|
|
Greater than or equal
to every member of set
|
>= ALL
|
!< ALL
|
Recommendations:
- Convert all !< and !> to
>= and <=
Convert the following in Microsoft SQL Server:
WHERE col1 !< 100
to this for Oracle:
WHERE col1 >= 100
- Convert like comparisons which
use [ ] and [^]
2. SELECT title
3. FROM titles
4. WHERE title like "[A-F]%"
- Change NULL constructs:
Table 2-12 shows that in Oracle, NULL is never equal to NULL. Change
the all = NULL constructs to IS NULL to retain the same functionality.
|
NULL Construct
|
Microsoft SQL
Server
|
Oracle
|
|
where col1 = NULL
|
depends on the data
|
FALSE
|
|
where col1 != NULL
|
depends on the data
|
TRUE
|
|
where col1 IS NULL
|
depends on the data
|
depends on the data
|
|
where col1 IS NOT NULL
|
depends on the data
|
depends on the data
|
|
where NULL = NULL
|
TRUE
|
FALSE
|
If
you have the following in Microsoft SQL Server:
WHERE col1 = NULL
Convert
it as follows for Oracle:
WHERE col1 IS NULL
|
Operator
|
Same in Both
Databases
|
Microsoft SQL
Server Only
|
Oracle Only
|
|
Add
|
+
|
||
|
Subtract
|
-
|
||
|
Multiply
|
*
|
||
|
Divide
|
/
|
||
|
Modulo
|
v
|
%
|
mod(x, y)
|
Recommendations:
Replace
any Modulo functions in Microsoft SQL Server with the mod() function in Oracle.
|
Operator
|
Same in Both
Databases
|
Microsoft SQL
Server Only
|
Oracle Only
|
|
Concatenate
|
s
|
+
|
||
|
|
Identify Literal
|
'this is a string'
|
"this is also a
string"
|
|
Recommendations:
Replace
all addition of strings with the || construct.
Replace
all double quotes string identifiers with single quote identifiers.
In
Microsoft SQL Server, an empty string ('') is interpreted as a single space in
INSERT or assignment statements on VARCHAR data. In concatenating VARCHAR,
CHAR, or TEXT data, the empty string is interpreted as a single space. The
empty string is never evaluated as NULL. You must bear this in mind when
converting the application.
|
Operator
|
Same in Both
Databases
|
Microsoft SQL
Server Only
|
Oracle Only
|
|
Distinct row from
either query
|
UNION
|
||
|
All rows from both
queries
|
UNION ALL
|
||
|
All distinct rows in
both queries
|
d
|
|
INTERSECT
|
|
All distinct rows in
the first query but not in the second query
|
d
|
|
MINUS
|
|
Operator
|
Same in Both
Databases
|
Microsoft SQL
Server Only
|
Oracle Only
|
|
bit and
|
&
|
||
|
bit or
|
|
|
||
|
bit exclusive or
|
^
|
||
|
bit not
|
~
|
Recommendations:
Oracle
enables you to write the procedures to perform bitwise operations.
If
you have the following Microsoft SQL Server construct:
X | Y :(Bitwise OR)
You
could write a procedure called dbms_bits.or (x,y) and convert the preceding
construct to the following in Oracle:
dbms_bits.or(x,y)
This
section compares the different functions used in the Microsoft SQL Server and
Oracle databases. It includes the following subsections:
|
Microsoft SQL
Server
|
Oracle
|
Description
|
|
ascii(char)
|
ascii(char)
|
Returns the ASCII
equivalent of the character.
|
|
char(integer_expression)
|
chr(integer_expression)
|
Converts the decimal
code for an ASCII character to the corresponding character.
|
|
charindex(specified_exp,
char_string)
|
instr(specified_exp,
char_string, 1, 1)
|
Returns the position
where the specified_exp first occurs in the char_string.
|
|
convert(data type,
expression, [format])
|
to_char, to_number,
to_date, to_label, chartorowid, rowtochar, hextochar, chartohex
|
Converts one data type
to another using the optional format. The majority of the functionality can
be matched. Refer to Oracle Database SQL Language Reference for more information.
|
|
datalength(expression)
|
g
|
Computes the length
allocated to an expression, giving the result in bytes.
|
|
difference(character_exp,
character_exp)
|
d
|
Returns the numeric
difference of the SOUNDEX values of the two strings.
|
|
isnull(variable,
new_value)
|
nvl(variable,
new_value)
|
If the value of the
variable is NULL, the new_value is returned.
|
|
lower(char_exp)
|
lower(char_exp)
|
Converts uppercase
characters to lowercase characters.
|
|
ltrim(char_exp)
|
ltrim(char_exp)
|
Truncates trailing
spaces from the left end of char_exp.
|
|
patindex(pattern,
|
column_name)
|
Returns the position
of the pattern in the column value. The pattern can have wild characters.
This function also works on TEXT and BINARY data types.
|
|
replicate(char_exp, n)
|
rpad(char_exp,
length(char_exp)*n, '')
|
Produces a string with
char_exp repeated n times.
|
|
reverse(char_string)
|
Reverses the
given char_string.
|
|
|
right(char_exp, n)
|
substr(char_exp,
(length(char_exp)
|
Returns the part of
the string starting at the position given by n from the right and extending
up to the end of the string.
|
|
rtrim(char_exp)
|
rtrim(char_exp)
|
Truncates the trailing
spaces from the right end of char_exp.
|
|
soundex(exp)
|
soundex(exp)
|
Returns phonetically
similar expressions to the specified exp.
|
|
space(int_exp)
|
rpad(' ', int_exp-1,
'')
|
Generates a string with
int_exp spaces.
|
|
str(float_exp, length)
|
to_char(float_exp)stuff(char_exp,
start, length, replace_str)substr(char_exp, 1, start) ||replace_str
||substr(char_exp, start+length)
|
Replaces a substring
within char_exp with replace_str.
|
|
substring(char_exp, start,
length)
Works on IMAGE and
TEXT data types
|
substr(char_exp,
start, length)
Does not work with
LONG and LONG_RAW data types
|
Replaces a substring
within char_exp with replace_str.
|
|
textptr(column_name)
|
d
|
Returns a pointer as a
varbinary(16) data type for a named IMAGE or TEXT column.
|
|
textvalid("column_name",
text_pointer)
|
h
|
Returns 1 if the
specified text_pointer is valid for the specified column_name. The column
must be of type TEXT or IMAGE.
|
|
upper(char_exp)
|
upper(char_exp)
|
Converts lowercase
characters to uppercase characters.
|
|
Microsoft SQL
Server
|
Oracle
|
Description
|
|
datalength(expression)
|
lengthb
|
Computes the length
allocated to an expression, giving the result in bytes.
|
|
isnull(variable,
new_value)
|
nvl(variable,
new_value)
|
If the value of the
variable is NULL, the new_value is returned.
|
Note:
The preceding functions tables list all the
Microsoft SQL Server character manipulation functions. They do not list all the
Oracle functions. There are many more Oracle character manipulation functions
that you can use.
Oracle
adds the ability to define functions. With this feature you can create Oracle
functions that match the name and function of Microsoft SQL Server functions.
|
Microsoft SQL
Server
|
Oracle
|
Description
|
|
dateadd(dd, int_exp,datetime_var)
|
date+int_exp
requires conversion of int_exp to a number of
days
|
Adds the int_exp
number of days to the date contained in datetime_var.
|
|
dateadd(mm, int_exp,datetime_var)
|
add_months(date, int_exp)
or
date+int_exp requires conversion of int_exp to
a number of days
|
Adds the int_exp
number of months to the date contained in datetime_var.
|
|
dateadd(yy, int_exp,datetime_var)
|
date+int_exp
requires conversion of int_exp to a number of
days
|
Adds the int_exp
number of years to the date contained in datetime_var.
|
|
datediff(dd, datetime1,datetime2)
|
date2-date1
|
Returns the difference
between the dates specified by the datetime1 and datetime2 variables. This
difference is calculated in the number of days.
|
|
datediff(mm, datetime1,datetime2)
|
months_between (date2, date1)
|
Returns the difference
between the dates specified by the datetime1 and datetime2 variables. This
difference is calculated in the number of months.
|
|
datediff(yy, datetime1,datetime2)
|
(date2-date1) /365.254
|
Returns the difference
between the dates specified by the datetime1 and datetime2 variables. This
difference is calculated in the number of years.
|
|
datename (datepart, date)
|
to_char(date, format)
|
Returns the specified
part of the date as an integer. The Microsoft SQL Server DATETIME has a
higher precision than Oracle DATE. For this reason, it is not always possible
to find an equivalent format string in Oracle to match the datepart in
Microsoft SQL Server. See the Data Types section of this chapter for more
information about conversion of the DATETIME data type.
|
|
datepart(datepart, date)
|
to_char(date, format)
|
Returns the specified
part of the date as a character string (name). The Microsoft SQL Server
DATETIME has a higher precision than Oracle DATE'. For this reason, it is not
always possible to find an equivalent format string in Oracle to match the
datepart in Microsoft SQL Server.
|
|
getdate()
|
sysdate
|
Returns the system
date.
|
Recommendations:
The
preceding table lists all the Microsoft SQL Server date manipulation functions.
It does not list all the Oracle date functions. There are many more Oracle date
manipulation functions that you can use.
It
is recommended that you convert most date manipulation functions to
"+" or "-" in Oracle.
Oracle
adds the ability to define functions. With this feature you can create Oracle
functions that match the name and functionality of all Microsoft SQL Server
functions. This is a useful feature, where users can call a PL/SQL function
from a SQL statement's SELECT LIST, WHERE clause, ORDER BY clause, and HAVING clause.
With the parallel query option, Oracle executes the PL/SQL function in parallel
with the SQL statement. Hence, users create parallel logic.
|
Microsoft SQL
Server
|
Oracle
|
|
abs(n)
|
abs(n)
|
|
acos(n)
|
acos(n)
|
|
asin(n)
|
|
|
atan(n)
|
atan(n)
|
|
atn2(n,m)
|
|
|
ceiling(n)
|
ceil(n)
|
|
cos(n)
|
cos(n)
|
|
cot(n)
|
|
|
degrees(n)
|
|
|
exp(n)
|
exp(n)
|
|
floor(n)
|
floor(n)
|
|
log(n)
|
ln(n)
|
|
log10(n)
|
log(base,number)
|
|
pi()
|
|
|
power(m,n)
|
power(m,n)
|
|
radians(n)
|
|
|
rand(n)
|
|
|
round(n[,m])
|
round(n[,m])
|
|
sign(n)
|
sign(n)
|
|
sin(n)
|
sin(n)
|
|
sqrt(n)
|
sqrt(n)
|
|
tan(n)
|
tan(n)
|
Recommendations:
The
preceding table lists all the Microsoft SQL Server number manipulation
functions. It does not list all the Oracle mathematical functions. There are
many more Oracle number manipulation functions that you can use.
Oracle
adds the ability to define functions. With this feature you can create Oracle
functions that match the name and functionality of all Microsoft SQL Server
functions. This is the most flexible approach. Users can write their own
functions and execute them seamlessly from a SQL statement.
Oracle
functions listed in the table work in SQL as well as PL/SQL.
This
section compares locking and transaction handling in the Microsoft SQL Server
and Oracle databases. It includes the following subsections:
Locking
serves as a control mechanism for concurrency. Locking is a necessity in a
multi-user environment because more than one user at a time may be working with
the same data.
|
Microsoft SQL
Server
|
Oracle
|
|
Microsoft SQL Server
locking is fully automatic and does not require intervention by users.
Microsoft SQL Server
applies exclusive locks for INSERT, UPDATE, and DELETE operations. When an
exclusive lock is set, no other transaction can obtain any type of lock on
those objects until the original lock is in place.
For non-update or read
operations, a shared lock is applied. If a shared lock is applied to a table
or a page, other transactions can also obtain a shared lock on that table or
page. However, no transaction can obtain an exclusive lock. Therefore,
Microsoft SQL Server reads block the modifications to the data.
Update locks:
Update locks are held
at the page level. They are placed during the initial stages of an update
operation when the pages are being read. Update locks can co-exist with
shared locks. If the pages are changed later, the update locks are escalated
to exclusive locks.
|
Oracle locking is
fully automatic and does not require intervention by users. Oracle features
the following categories of locks:
Data locks (DML locks)
to protect data.The "table locks" lock the entire table and
"row locks" lock individual rows.
Dictionary locks (DDL
locks) to protect the structure of objects.
Internal locks to
protect internal structures, such as files.
DML operations can
acquire data locks at two different levels; one for specific rows and one for
entire tables.
Row-level locks:
An exclusive lock is
acquired for an individual row on behalf of a transaction when the row is
modified by a DML statement. If a transaction obtains a row level lock, it
also acquires a table (dictionary) lock for the corresponding table. This
prevents conflicting DDL (DROP TABLE, ALTER TABLE) operations that would
override data modifications in an on-going transaction.
|
|
Intent locks:
Microsoft SQL Server
locking is fully automatic and does not require intervention by users.
Microsoft SQL Server applies exclusive locks for INSERT, UPDATE, and DELETE
operations. When an exclusive lock is set, no other transaction can obtain
any type of lock on those objects until the original lock is in place. For
non-update or read operations, a shared lock is applied. If a shared lock is
applied to a table or a page, other transactions can also obtain a shared
lock on that table or page. However, no transaction can obtain an exclusive
lock. Therefore, Microsoft SQL Server reads block the modifications to the
data.
Extent locks:
Extent locks lock a
group of eight database pages while they are being allocated or freed. These
locks are held during a CREATE or DROP statement, or during an INSERT that
requires new data or index pages.
A list of active locks
for the current server can be seen with SP_LOCK system procedure.
|
Table-level data locks
can be held in any of the following modes:
Row share table lock
(RW):
This indicates that
the transaction holding the lock on the table has locked rows in the table
and intends to update them. This prevents other transactions from obtaining
exclusive write access to the same table by using the LOCK TABLE table IN
EXCLUSIVE MODE statement. Apart from that, all the queries, inserts, deletes,
and updates are allowed in that table.
Row exclusive table
lock (RX):
This generally
indicates that the transaction holding the lock has made one or more updates
to the rows in the table. Queries, inserts, deletes, updates are allowed in
that table.
Share lock (SL):
Share row exclusive
lock(SRX)
Exclusive lock (X):
The dynamic
performance table V$LOCK keeps the information about locks.
|
Recommendations:
In
Microsoft SQL Server, SELECT statements obtain shared locks on pages/rows. This
prevents other statements from obtaining an exclusive lock on those pages/rows.
All statements that update the data need an exclusive lock. This means that the
SELECT statement in Microsoft SQL Server blocks the UPDATE statements as long
as the transaction that includes the SELECT statement does not commit or
rollback. This also means that two transactions are physically serialized
whenever one transaction selects the data and the other transaction wants to
change the data first and then select the data again. In Oracle, however,
SELECT statements do not block UPDATE statements, since the rollback segments
are used to store the changed data before it is updated in the actual tables.
Also, the reader of the data is never blocked in Oracle. This allows Oracle transactions
to be executed simultaneously.
If
Microsoft SQL Server logical transactions are automatically translated to
Oracle logical transactions, the preceding transactions that execute properly
in Microsoft SQL Server as they are serialized cause a deadlock in Oracle.
These transactions should be identified and serialized to avoid the deadlock.
These transactions are serialized in Microsoft SQL Server as INSERT, UPDATE,
and DELETE statements block other statements.
|
Microsoft SQL
Server
|
Oracle
|
|
Microsoft SQL Server
does not have a row-level locking feature.
Microsoft SQL Server
applies a page-level lock, which effectively locks all rows on the page,
whenever any row in the page is being updated. This is an exclusive lock
whenever the data is being changed by DML statements.
Microsoft SQL Server
7.0 implements a form of row-level locking.
Microsoft SQL Server
7.0 escalates locks at row level to page level automatically.
SELECT statements are
blocked by exclusive locks that lock an entire page.
|
Oracle has a
row-locking feature. Only one row is locked when a DML statement is changing
the row.
|
Recommendations:
No
changes are required to take advantage of the row-level locking feature of
Oracle.
|
Microsoft SQL
Server
|
Oracle
|
|
Microsoft SQL Server
provides the HOLDLOCK function for transaction-level read consistency.
Specifying a SELECT with HOLDLOCK puts a shared lock on the data. More than
one user can execute a SELECT with HOLDLOCK at the same time without blocking
each other.
When one of the users
tries to update the selected data, HOLDLOCK blocks the update until the other
users commit, rollback, or attempt an update and a deadlock occurs. This
means that HOLDLOCK prevents other transactions from updating the same data
until the current transaction is in effect.
|
Read consistency as
supported by Oracle does the following:
To provide read
consistency, Oracle creates a read-consistent set of data when a table is
being read and simultaneously updated.
Read consistency
functions as follows:
You can specify that a
transaction be read only using the following command:
SET TRANSACTION READ ONLY
|
|
Microsoft SQL
Server
|
Oracle
|
|
After completion, any
statement not within a transaction is automatically committed.A statement can
be a batch containing multiple T-SQL statements that are sent to the server
as one stream. The changes to the database are automatically committed after
the batch executes. A ROLLBACK TRAN statement subsequently executed has no
effect. In Microsoft SQL Server, transactions are not implicit. Start logical
transaction with a BEGIN TRANSACTION clause. Logical transactions can be
committed or rolled back as follows.
BEGIN TRANSACTION [transaction_name]
Use COMMIT TRAN to
commit the transaction to the database. You have the option to specify the
transaction name. Use ROLLBACK TRAN to roll back the transaction. You can set
savepoints to roll back to a certain point in the logical transaction using
the following command:
SAVE TRANSACTION savepoint_name
Roll back to the
specified SAVEPOINT with the following command:
ROLLBACK TRAN <savepoint_name>
|
Statements are not
automatically committed to the database. The COMMIT WORK statement is
required to commit the pending changes to the database.
Oracle transactions
are implicit. This means that the logical transaction starts as soon as data
changes in the database.
COMMIT WORK commits
the pending changes to the database.
ROLLBACK undoes all
the transactions after the last COMMIT WORK statement.
Savepoints can be set
in transactions with the following command:
SET SAVEPOINT savepoint_name
The following command
rolls back to the specified SAVEPOINT:
ROLLBACK <savepoint_name>
Two-phase commit is
automatic and transparent in Oracle. Two-phase commit operations are needed
only for transactions which modify data on two or more databases.
|
|
Microsoft SQL Server
allows you to nest BEGIN TRAN/COMMIT TRAN statements. When nested, the outermost
pair of transactions creates and commits the transaction. The inner pairs
keep track of the nesting levels. A ROLLBACK command in the nested
transactions rolls back to the outermost BEGIN TRAN level, if it does not
include the name of the SAVEPOINT. Most Microsoft SQL Server applications
require two-phase commit, even on a single server. To see if the server is
prepared to commit the transaction, use PREPARE TRAN in two-phase commit
applications.
Completed transactions
are written to the database device at CHECKPOINT. A CHECKPOINT writes all
dirty pages to the disk devices since the last CHECKPOINT.
Calls to remote
procedures are executed independently of any transaction in which they are
included.
|
When a CHECKPOINT
occurs, the completed transactions are written to the database device. A
CHECKPOINT writes all dirty pages to the disk devices that have been modified
since last checkpoint
|
Recommendations:
Transactions
are not implicit in Microsoft SQL Server. Therefore, applications expect that
every statement they issue is automatically committed it is executed.
Oracle
transactions are always implicit, which means that individual statements are
not committed automatically. When converting a Microsoft SQL Server application
to an Oracle application, care needs to be taken to determine what constitutes
a transaction in that application. In general, a COMMIT work statement needs to
be issued after every "batch" of statements, single statement, or
stored procedure call to replicate the behavior of Microsoft SQL Server for the
application.
No comments:
Post a Comment