Oracle vs. SQL Server
Version 10.2 vs. 2005
 
SQL Server 2005 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.
- Real Application Cluster
Cluster Server Data Guard
Standby Server Standby Server
Bulk Insert SQL*Loader
Primary Data File System Data File
Secondary Data File Combination of Data Files
Log File Log Files Not Used For Rollback

Note: The log file concept and architecture are completely different
Truncate Transaction Logs No Equivalent: Not An Issue
Logical File Name Tablespace
 
Object Types

Clusters

no equivalent Cluster by Hash
no equivalent Cluster by Index
no equivalent Sorted Hash Cluster
 

 Constraints

Primary Key Primary Key
Unique Key Unique
Foreign Key Referential (Foreign Key)
Foreign Key On Delete Cascade On Delete Cascade
Foreign Key Set Null On Delete Set Null
no equivalent Deferrable
no equivalent RELY / NORELY
no equivalent VALIDATE / NOVALIDATE
Check Check
NULL / NOT NULL NULL / NOT NULL
no equivalent Read Only
no equivalent REF (Nested Table Constraint)
 

Database Linkages

Linked Server Database (DB) Link
 

Dimension

? Dimension
 

Functions (user defined)

Aggregate Function
Function Function
Table Function Pipelined Table Function
IN and OUT Parameters Only IN, OUT, and IN-OUT Parameters
Parameter Default Parameter Default
 

Indexes

Index B*Tree
no equivalent Bitmap
no equivalent Bitmap Join
no equivalent Cluster
Clustered Index Index Organized Table
no equivalent Compressed
Descending Descending
can be imitated with a computed column Function Based
Global Global
Local Local
no equivalent No Segment (Virtual)
Non-Unique Non-Unique
no equivalent Reverse
no equivalent REF
Unique Unique
 

Libraries

Assembly Library
 

Materialized Views

Indexed View Materialized Views (multiple types)
 

Operators (user defined)

no equivalent Operator
no equivalent Overloading
 

Packages

no equivalent Package Header
no equivalent Package Body
no equivalent Initialization Section
no equivalent Overloading
no equivalent Serial Reusability
 

Procedures (user defined)

Procedure Stored Procedure
IN and OUT Parameters Only IN, OUT, and IN-OUT Parameters
Parameter Default Parameter Default
no equivalent NOCOPY
no equivalent AUTHID
 

Rules

deprecated CHECK Constraint
 

Schemas

Schema Schema
 

Surrogate Key Generator

no equivalent Sequence
Identity no equivalent
 

Synonyms

Synonym Private Synonym
no equivalent Public Synonym
Creatable for limited object types Creatable for all object types
 

Tables

Table Heap Table
Cluster Index Index Organized Table
Column Default Column Default
no equivalent Compressed Table
Computed Column Function Based Index
no equivalent External Table
no equivalent Global Temporary Table
(con commit delete rows)
no equivalent Global Temporary Table
(on commit preserve rows)
no equivalent Nested Table
no equivalent Hash Partitioned Table
no equivalent List Partitioned Table
with Create Partition Function
and Create Partition Schema		 Range Partitioned Table
no equivalent Subpartitioned Table
Temporary Table not required due to MVCC
no equivalent XML Table
no equivalent PCTUSED
FILLFACTOR PCTFREE
no equivalent INITRANS
no equivalent MAXTRANS
 

Types

Type Type
no equivalent Type Header
no equivalent Type Body with Methods
no equivalent Object
no equivalent VArray
 

Views

View View
Check Option Check Option
ORDER BY (only with TOP clause) ORDER BY
 
Built-in Operators

Arithmetic Operators

+ (add) + (add)
- (subtract) - (subtract)
* (multiply) * (multiply)
/ (divide) / (divide)
% (modulo) mod function
power function ** (power)

Assignment Operators

= (equals)

:= (colon equals)

Bitwise Operators

& (bitwise AND) utl_raw.bit_and
| (bitwise OR) utl_raw.bit_or
^ (bitwise exclusive OR) utl_raw.bit_xor
~ (bitwise NOT) no equivalent
no equivalent utl_raw.bit_complement

Comparison Operators

= (equal to) = (equal to)
> (greater than) > (greater than)
< (less than) < (less than)
>= (greater than or equal to) >= (greater than or equal to)
<= (less than or equal to) <= (less than or equal to)
<> (not equal to) <> (not equal to)
!= (not equal to) != (not equal to)
<> (not equal to) =^ (not equal to)
<> (not equal to) ~= (not equal to)
!< (not less than) no equivalent
!> (not greater than) no equivalent

Date Operators

no equivalent + (add)
no equivalent - (subtract)

Hierarchical Operators

no equivalent CONNECT
no equivalent CONNECT BY
no equivalent CONNECT BY PRIOR
no equivalent CONNECT BY ROOT

Conditions

ALL ALL
AND AND
ANY ANY
BETWEEN BETWEEN
no equivalent DEPTH
no equivalent EMPTY
no equivalent EQUALS_PATH
EXISTS EXISTS
IN INFINITE
no equivalent IS A SET
no equivalent IS ANY
no equivalent IS NULL
no equivalent IS OF ONLY
no equivalent IS OF TYPE
no equivalent IS PRESENT
LIKE LIKE
no equivalent MEMBER OF
no equivalent NAN
NOT NOT
OR OR
no equivalent PATH
SOME SOME
no equivalent SUBMULTISET OF
no equivalent UNDER_PATH

Multiset Operators

no equivalent MULTISET
no equivalent MULTISET EXCEPT
no equivalent MULTISET INTERSECT
no equivalent MULTISET UNION

Set Operators

INTERSECT INTERSECT
EXCEPT MINUS
UNION UNION
UNION ALL UNION ALL

String Operators

+ (concatenation) || (concatenation)

Unary Operators

+ (positive) + (positive)
- (negative) - (negative)
 
Triggers

DDL Triggers

ALL SERVER DATABASE
ALTER ALTER
no equivalent ASSOCIATE STATISTICS
no equivalent AUDIT
no equivalent COMMENT
CREATE CREATE
DATABASE SCHEMA
DENY (not irrelevant)
no equivalent DDL
no equivalent DISASSOCIATE STATISTICS
DROP DROP
GRANT GRANT
no equivalent NOAUDIT
no equivalent RENAME
REVOKE REVOKE
no equivalent SUSPEND
no equivalent TRUNCATE
UPDATE STATISTICS ANALYZE

Instead-Of Triggers

INSTEAD-OF TRIGGER INSTEAD-OF TRIGGER

System Triggers

no equivalent DATABASE
no equivalent SCHEMA
AFTER LOGON AFTER LOGON
no equivalent AFTER SERVERERROR
no equivalent AFTER STARTUP
no equivalent BEFORE LOGOFF
no equivalent BEFORE SHUTDOWN

Table Triggers

no equivalent before insert statement level
no equivalent before update statement level
no equivalent before delete statement level
no equivalent before insert row level
no equivalent before update row level
no equivalent before delete row level
after insert trigger after insert statement level
after update trigger after update statement level
after delete trigger after delete statement level
no equivalent after insert row level
no equivalent after update row level
no equivalent after delete row level
no equivalent OF Clause
no equivalent REFERENCING Clause
with encryption native compilation and wrap
 
Functions

Analytic Functions

Running average AVG AVG
Coefficient of correlation no equivalent CORR
Running count by partition COUNT & COUNT_BIG COUNT
Population covariance of a set of pairs no equivalent COVAR_COUNT
Sample covariance of a set of pairs no equivalent COVAR_SAMP
Cumulative distribution in a group no equivalent CUME_DIST
Rank within a group without gaps DENSE_RANK DENSE_RANK
Row ranked first by DENSE RANK no equivalent FIRST
First value of an ordered set no equivalent FIRST_VALUE
Provides access to a row by offset no equivalent LAG
Row ranked last by DENSE RANK no equivalent LAST_VALUE
Last value of an ordered set no equivalent LAST_VALUE
Provides access to a row by offset no equivalent LEAD
Maximum value by partition MAX MAX
Minimum value by partition MIN MIN
Divides an ordered dataset into buckets NTILE NTILE
Rowset partitioning OVER OVER
Calculates the value of r-1/rows-1 no equivalent PERCENT_RANK
An inverse distribution function no equivalent PERCENTILE_CONT
An inverse distribution function no equivalent PERCENTILE_DISC
Rank of a value in a group RANK RANK
Computes ratio of a value to the sum of a set no equivalent RATIO_TO_REPORT
Linear regression function no equivalent REGR_AVGX
Linear regression function no equivalent REGR_AVGY
Linear regression function no equivalent REGR_COUNT
Linear regression function no equivalent REGR_INTERCEPT
Linear regression function no equivalent REGR_R2
Linear regression function no equivalent REGR_SLOPE
Linear regression function no equivalent REGR_SXX
Linear regression function no equivalent REGR_SXY
Linear regression function no equivalent REGR_SYY
Assigns row numbers by partition ROW_NUMBER ROW_NUMBER
Sample standard deviation STDEV STDDEV
Square root of the population variance STDEVP STDDEV_POP
Cumulative sample standard deviation no equivalent STDDEV_SAMP
Cumulative running total SUM SUM
Population variance of a set VARP VAR_POP
Sample variance of a set no equivalent VAR_SAMP
Variance of an expression VAR VARIANCE

Collection Functions

Number of elements in a nested table no equivalent CARDINALITY
Creates a nested table from selected rows no equivalent COLLECT
Creates a nested table of nonempty subsets no equivalent POWERMULTISET
As above: Of the specified cardinality no equivalent POWERMULTISET_BY_CARDINALITY
Converts a nested table into a unique set no equivalent SET

Conversion Functions

ASCII string into the DB character set no equivalent ASCIISTR
BFILE from directory + file name  no equivalent BFILENAME
Bitvector to a number no equivalent BIN_TO_NUM
One data type to another CAST & CONVERT CAST
String to a ROWID not relevant CHARTOROWID
String to a unicode string NCHAR COMPOSE
One character set to another no equivalent CONVERT
Unicode string to a string no equivalent DECOMPOSE
Char containing hexadecimal digits to raw no equivalent HEXTORAW
Number into a Day-to-Second interval no equivalent NUMTODSINTERVAL
Number into a Year-to-Month interval no equivalent NUMTOYMINTERVAL
A value to its hash no equivalent ORA_HASH
Convert RAW to CHAR no equivalent RAW_TO_CHAR
Raw into a hexadecimal containing string no equivalent RAWTOHEX
Convert RAW to NCHAR no equivalent RAW_TO_NCHAR
Raw into a hexadecimal containing 'N' string no equivalent RAWTONHEX
Converts RAW to variable length string no equivalent RAW_TO_VARCHAR2
Raw into a hexadecimal object REF no equivalent REFTOHEX
ROWID to CHAR not relevant ROWIDTOCHAR
ROWID to NCHAR not relevant ROWIDTONCHAR
Timestamp to its SCN equivalent not relevant SCNTOTIMESTAMP
Converts RAW to NUMBER no equivalent SYS_OP_RAWTONUM
Casts an array as a table no equivalent TABLE
An SCN to its timestamp equivalent not relevant TIMESTAMPTOSCN
Convert to BINARY_DOUBLE data type no equivalent TO_BINARYDOUBLE
Convert to BINARY_FLOAT data type no equivalent TO_BINARYFLOAT
Convert to CHAR or VARCHAR2 data type STR TO_CHAR
Convert to CLOB data type no equivalent TO_CLOB
Convert to DATE data type no equivalent TO_DATE
Convert to Day-to-Second Interval data type no equivalent TO_DSINTERVAL
LONG or LONG RAW to LOB data type no equivalent TO_LOB
Single byte to corresponding multi-byte no equivalent TO_MULTI_BYTE
Convert to NCHAR data type no equivalent TO_NCHAR
Convert to NCLOB data type no equivalent TO_NCLOB
Convert to NUMBER data type no equivalent TO_NUMBER
Multi-byte to corresponding single byte no equivalent TO_SINGLE_BYTE
Convert to TIMESTAMP data type no equivalent TO_TIMESTAMP
To TIMESTAMP WITH TIMEZONE data type no equivalent TO_TIMESTAMP_TZ
Convert to Year-to-Month Interval data type no equivalent TO_YMINTERVAL
Changes character set no equivalent TRANSLATE_USING
Integer value based on Unicode standard UNICODE no equivalent
String to UTF8 or UTF16 no equivalent UNISTR

Date-Time Functions

Date addition DATEADD +
Date subtraction DATEDIFF -
Add a month no equivalent ADD_MONTHS
First non-null value COALESCE COALESCE
Current date and time (low precision) GETDATE
CURRENT_DATE		 CURRENT_DATE
Current date and time (high precision) no equivalent CURRENT_TIMESTAMP
Current date and time (low second) GETDATE SYSDATE
Current date and time (high precision) no equivalent SYSTIMESTAMP
Database's time-zone no equivalent DBTIMEZONE
Extract part from date-time or interval no equivalent EXTRACT
Alter time zone information no equivalent FROM_TZ
Largest of a set of dates no equivalent GREATEST
Last day of month no equivalent LAST_DAY
Smallest of a set of dates no equivalent LEAST
Months between dates no equivalent MONTHS_BETWEEN
Time zone conversion no equivalent NEW_TIME
First weekday after date no equivalent NEXT_DAY
Rounds date to unit specified  no equivalent ROUND
Current session's time zone no equivalent SESSIONTIMEZONE
Coordinated universal time GET_UTC_DATE SYS_EXTRACT_UTC
Convert date part to name DATENAME TO_CHAR
Convert date part to number DATEPART TO_CHAR
Integer representing the day of the week DAY TO_CHAR
Integer representing the month of the year MONTH TO_CHAR
Integer representing the year YEAR TO_CHAR
Convert string to date CAST TO_DATE
Determine if a value/expression is a valid date ISDATE TO_DATE
Date from date-time no equivalent TRUNC
Time-zone offset no equivalent TZ_OFFSET

Data Mining Functions

Cluster ID of the cluster with highest probability no equivalent CLUSTER_ID
Degree of confidence of membership of a row no equivalent CLUSTER_PROBABILITY
Varray of objects of possible clusters no equivalent CLUSTER_SET
Feature ID with highest coefficient value no equivalent FEATURE_ID
Varray of objects of all possible features no equivalent FEATURE_SET
Value of a given feature no equivalent FEATURE_VALUE
Best prediction for the specified model no equivalent PREDICTION
Cost measure of a given prediction no equivalent PREDICTION_COST
XML with model specific scoring no equivalent PREDICTION_DETAILS
Probability for a given prediction no equivalent PREDICTION_PROBABILITY
Varray of objects with all possible classes no equivalent PREDICTION_SET

Environment Functions

Database / Schema Identifier DB_ID SYS_CONTEXT
Database Name DB_NAME SYS_CONTEXT
Host Identifier HOST_ID SYS_CONTEXT
Workstation Name HOST_NAME SYS_CONTEXT
Local language identifier @@LANGID SYS_CONTEXT
Name of language in use @@LANGUAGE SYS_CONTEXT
Value with the named context namespace no equivalent SYS_CONTEXT
User Session ID @@SPID UID
User name CURRENT_USER USER
Schema ID SCHEMA_ID SYS_CONTEXT
Schema name SCHEMA_NAME SYS_CONTEXT
Username in the current context SESSION_USER SYS_CONTEXT
Value with the named context namespace no equivalent USERENV

Miscellaneous Functions

Null BLOB no equivalent EMPTY_BLOB
Null CLOB no equivalent EMPTY_CLOB
Length of an NCHAR column no equivalent NLS_CHARSET_DECL_LEN
ID of NLS character set no equivalent NLS_CHARSET_ID
Name of NLS character set from ID no equivalent NLS_CHARSET_NAME
Numeric identifier of current exception code @@ERROR & ERROR_NUMBER SQLCODE
Error message of current exception code no equivalent SQLERRM
Hierarchical path of column from root to node no equivalent SYS_CONNECT_BY_PATH
16 byte GUID no equivalent SYS_GUID
Function that builds descending index values no equivalent SYS_OP_DESCEND
Index leaf block ID scan no equivalent SYS_OP_LBID

Model Functions

Use left side value on right side calculation no equivalent CV
Iterate through data no equivalent ITERATE
Iterate a set number of times through data no equivalent ITERATE_UNTIL
Current iteration number no equivalent ITERATION_NUMBER
Returns expr1 prior to execution no equivalent PRESENTNNV
Returns expr1 prior to execution no equivalent PRESENTV
Reference prior model values no equivalent PREVIOUS

Null Handling Functions

Evaluate one or both operands may be NULL no equivalent LNNVL
Returns NULL is expr1 and expr2 both NULL NULLIF NULLIF
Convert to string if NULL ISNULL NVL
Substitute if NULL or if NOT NULL no equivalent NVL2
Map NULL for joins (not relevant) SYS_OP_MAP_NONNULL

Numeric Handling Functions

Absolute value ABS ABS
Arc cosine ACOS ACOS
Arc sine ASIN ASIN