Note:
The purpose of this page is to try to objectively map capabilities
between two different products not to misrepresent either. If you are aware
of errors or omissions please contact us and let us know.
Thank you.

SQL Server
2008

Oracle 10gR2

Verbiage

Instance

Database

Instance

Database

Schema

User

User

System & User Databases

master

model

tempdb

msdb

Schemas

SYS and SYSTEM
schemas

SYS and SYSTEM
schemas

temporary tablespace

stored in SYS schema

Storage
Concepts

Row

Page

Extent (always
64K)

-

Primary
Datafile

-

FileGroup

-

Storage
Concepts

Row

Block

Extent (user
defined sizing)

Segment

Data File

Tempfile

Tablespace
(usable by multiple schemas)

Default
Tablespace

Note:
The the way primary and secondary datafiles are mapped in SQL Server does
not relate to how data files are mapped in Oracle.

SQL Server has a totally
different internal structure than Oracle has. In SQL Server, a table is basically a big
linked-list and the data blocks are essentially the leaf-blocks of the cluster index.
Those blocks are then doubly-linked back and forth so you can traverse the table in a full
table scan or in an index range scan. In fact, an
index range scan of the whole table is essentially (physically) the same as a full table
scan. I'm not sure exactly why, but SQL Server has always had trouble with corruption of
these link-list pointers.
Run DBCC to check (and fix) problems with these pointers.

No equivalent issue

Wildcards

Wildcards

SQL Server

Oracle

Locks are a limiting resource

Unlimited locks

Lock escalation

No lock escalation

Default: Reads block writes, writes block reads

Reads do not block writes, writes do not block reads

Autonumering identity column

Sequence Objects

All table triggers are AFTER

BEFORE and AFTER triggers

All table triggers are STATEMENT TRIGGERS

Triggers can be statement or row level

All pages are 8K

Blocks can be 2K, 4K, 8K, 16K, or 32K

All extents 64K

Extents in any multiple of the
block size

No equivalent

Create or Replace Syntax

Single Operating System: Windows

Windows, Linux, UNIX, OS/390
Sun Sequent VAX-VMX DEC MVS

Triggers commit independent of triggering event

Triggers can not commit or rollback independently
NOTE: There is an exception to this- if you use PRAGMA AUTONOMOUS_TRANSACTION a trigger can commit and
rollback. Even if PRAGMA AUTONOMOUS_TRANSACTION doesn't work to commit
or rollback the statement that fired the trigger, it can commit other
DML written inside it.