Oracle Database 11gR2 - Microsoft SQL Server 2008 Comparison
Version 11.2.0.3
 
General Information
Note: The purpose of this page is to objectively map capabilities between two different products not to misrepresent either. If you are aware of errors or omissions please bring them to my attention. When you do so please provide working code or document links that can be used to verify the information provided. Thank you.
 
Basic Concepts and Verbiage
SQL Server 2008 Oracle 11gR2
Instance Database & Instance
Database Schema
User User
master SYS and SYSTEM schemas, SYSTEM and SYSAUX tablespaces
model SYS and SYSTEM schemas, SYSTEM and SYSAUX tablespaces
tempdb no schema, TEMPORARY tablespace
msdb SYS and SYSTEM schemas, SYSTEM and SYSAUX tablespaces
Log File Log Files (only used for recovery ... never rollback)
Log File Undo (undo tablespace)
Truncate Transaction Logs No Equivalent Not Reqired By Design
TransactSQL / TSQL PL/SQL
Derived Table In-line View
 
Processes
SQL Server 2008 Oracle 11gR2
No Similar Concept ARCn (Archiver)
LazyWriter: Writes dirty blocks DBWn (DBWriter): Never writes dirty blocks
 
Storage Concepts
SQL Server 2008 Oracle 11gR2
Logical File Name Tablespace
Row Row
Page always 8K) Block (2K, 4K, 8K, 16K, or 32K)
Extent (always 64K) Extent (any user defined multiple of the block size)
No Similar Concept Segment
Primary Datafile Data File
Secondary Datafile No Similar Concept (all datafiles are equal)
tempdb Temporary Tablespace
FileGroup (mapped to a single database) Tablespace (mapped to one or many schemas)
No Similar Concept Default Tablespace
No Similar Concept Tablespace Group
Note: The the way primary and secondary datafiles are mapped in SQL Server does not relate to how data files are mapped in Oracle. Also different is that in Oracle storage at the logical (tablespace) level or physical (data file) level is not owned. Rather privileges by means of a quota can be granted to any user/schema.

Note: SQL Server has a very different internal structure than Oracle. 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 comparable issue exists within Oracle.
 
Feature Names
SQL Server 2008 Oracle 11gR2
Bulk Insert SQL*Loader
No Equivalent Technology Bulk Insert
Cluster Server (missing many Data Guard features) Data Guard Physical Standby
No Equivalent Technology Active Data Guard
Standby Server Data Guard Logical Standby
No Equivalent Technology Data Guard Snapshot Standby
No Equivalent Technology Real Application Clusters
No Equivalent Technology Edition Based Redefinition
No Equivalent Technology Flashback Archive
No Equivalent Technology Flashback Database
No Equivalent Technology Flashback Drop
No Equivalent Technology Flashback Query
No Equivalent Technology Flashback Table
No Equivalent Technology Flashback Transaction
No Equivalent Technology Flashback Version
No Equivalent Technology Transaction Backout
 
Object Types
SQL Server 2008 Oracle 11gR2
Cluster  
No Equivalent Object Cluster by Hash
No Equivalent Object Cluster by Index
No Equivalent Object 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 Linkage  
Linked Server Database (DB) Link
DDL Event Triggers  
ALL SERVER DATABASE
ALTER ALTER
No Equivalent ASSOCIATE STATISTICS
No Equivalent AUDIT
No Equivalent COMMENT
CREATE CREATE
DATABASE SCHEMA
DENY Not Relevant To The Security Model
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
Dimension  
No Equivalent Dimension
Function: 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
No Equivalent Result Cache
Group By Clause  
GROUP BY GROUP BY
No Equivalent GROUP_ID
GROUPING_ID GROUPING_ID
GROUPING  GROUPING
GROUPING SETS GROUPING SETS
ROLLUP ROLLUP
CUBE CUBE
Indexes  
Index B*Tree
No Equivalent Bitmap
No Equivalent Bitmap Join
No Equivalent Cluster
Clustered Index Index Organized Table
No Equivalent Compressed
Descending Descending
Filtered Duplicates Oracle Normal B*Tree Functionality
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
Instead-Of Triggers  
INSTEAD-OF TRIGGER INSTEAD-OF TRIGGER
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 Pragma Serially Reusable
Procedures (User Defined)  
Procedure Procedure
IN Parameter IN Parameter
OUT Parameter OUT Parameter
No Equivalent IN OUT Parameter
Parameter Default Parameter Default
No Equivalent NOCOPY
No Equivalent Functionality AUTHID (definer vs. current_user rights)
Rules  
deprecated CHECK Constraint
Schemas  
Schema Schema
Surrogate Key Generation  
Identity No Equivalent Functionality
No Equivalent Functionality Sequence
Synonyms  
Synonym Private Synonym
No Equivalent Functionality Public Synonym
Creatable For Limited Object Types Creatable For All Object Types
System Event Triggers  
No Equivalent Functionality Database
No Equivalent Functionality Schema
After Logon After Logon
No Equivalent Functionality After Server Error
No Equivalent Functionality After Startup
No Equivalent Functionality Before Log Off
No Equivalent Functionality Before Shut Down
Tables  
Table Heap Table
Temporary Table No User Defined Equivalent Functionality
Cluster Index Index Organized Table
Column Default Column Default
No Equivalent Functionality Compressed Table
No Equivalent Functionality External Table
No Equivalent Functionality Global Temporary Table (on commit delete rows)
No Equivalent Functionality Global Temporary Table (on commit preserve rows)
No Equivalent Functionality Nested Table
No Equivalent Functionality Hash Partitioned Table
No Equivalent Functionality Interval Partitioned Table
No Equivalent Functionality List Partitioned Table
with Create Partition Function and Schema Range Partitioned Table
No Equivalent Functionality Reference Partitioned Table
No Equivalent Functionality Composite (Subpartitioned) Table
No Equivalent Functionality System Partitioned Table
No Equivalent Functionality Read Only Table
No Equivalent Functionality XML Table
No Equivalent Functionality PCTUSED
FILLFACTOR PCTFREE
No Equivalent Functionality INITRANS
No Equivalent Functionality MAXTRANS
Computed Column Virtual Column
No Equivalent Functionality Virtual Column Partitioned Table
Tables Triggers  
No Equivalent Functionality Before Insert Statement Level
No Equivalent Functionality Before Update Statement Level
No Equivalent Functionality Before Delete Statement Level
No Equivalent Functionality Before Insert Row Level
No Equivalent Functionality Before Update Row Level
No Equivalent Functionality Before Delete Row Level
After Insert after Insert Statement Level
After Update After Update Statement Level
After Delete After Delete Statement Level
No Equivalent Functionality After Insert Row Level
No Equivalent Functionality After Update Row Level
No Equivalent Functionality After Delete Row Level
No Equivalent Functionality OF Clause
No Equivalent Functionality REFERENCING Clause
No Equivalent Functionality WHEN Clause
No Equivalent Functionality Compound Trigger
No Equivalent Functionality Follows Clause
No Equivalent Functionality Preceding Clause
With Encryption Wrap
Type  
Type Type
User Defined Table Type (limited equiv) Type Header
No Equivalent Functionality Type Body with Methods
No Equivalent Functionality Object
No Equivalent Functionality VArray
View  
View View
Check Option Check Option
ORDER BY (only with TOP clause) ORDER BY
SELECT DISTINCT NOT ALLOWED SELECT DISTINCT ALLOWED
 
Data Types
Description SQL Server 2008 Oracle 11gR2
String (Character) Types
Fixed length string CHAR (8K) CHAR (2K)
Fixed length string NCHAR (8K) NCHAR (2K)
Variable length string CHAR (8K) CHARACTER (2K)
Variable length string VARCHAR (8K) VARCHAR2 (32K)
Variable length string NVARCHAR (8K) NVARCHAR2 (32K)
Variable length string VARCHAR (8K) STRING (32K)
Variable length string No Equivalent Data Type LONG (2GB)
Variable length string VARCHAR(MAX) CLOB (128TB)
Numeric Data Types
Integer BIT NUMBER(1,0)
Integer TINYINT (1 byte) SMALLINT, INT, INTEGER, BINARY INTEGER, and PLS_INTEGER
(all up to 38 digits)
Integer SMALLINT (2 bytes)
Integer INT (4 bytes)
Integer BIGINT (8 bytes)
Number DECIMAL (1 byte) DEC, DECIMAL, NUMERIC, NUMBER
(up to 38 digits)
Number NUMERIC (2 bytes)
Floating point numbers FLOAT FLOAT
Floating point numbers REAL REAL (63 binary digits)
Floating point numbers No Equivalent Data Type DOUBLE_PRECISION (126 binary digits)
Floating point numbers No Equivalent Data Type FLOAT (126 binary digits)
Floating point numbers using native machine arithmetic No Equivalent Data Type BINARY_FLOAT (32 bit)
Floating point numbers using native machine arithmetic No Equivalent Data Type BINARY_DOUBLE (64 bit)
Non-negative integers No Equivalent Data Type NATURAL
Not nullable non-negative integers No Equivalent Data Type NATURALN
Only positive integers No Equivalent Data Type POSITIVE
Not nullable non-negative integers No Equivalent Data Type POSITIVEN
-1, 0 or +1 only No Equivalent Data Type SIGNTYPE
Monetary Data Types
Windows-centric formatted display type SMALLMONEY NUMBER(14,4)
Windows-centric formatted display type MONEY NUMBER(19,4)
Date, Interval, Time, and Timezone Data Types
Date-Time (low precision) SMALLDATETIME & DATE DATE (to 1 sec)
Date-Time (high precision) DATETIME & DATETIME2 TIMESTAMP (to 1 nanosecond)
Date-Time with Timezone
(precision hh:mm:ss.nnnnnnn)		 DATETIMEOFFSET TIMESTAMP WITH TIMEZONE
Date-Time with Local Timezone
(precision hh:mm:ss.nnnnnnn)		 No Equivalent Datatype TIMESTAMP WITH LOCAL TIMEZONE
Date-Time Interval (precision mo) No Equivalent Datatype INTERVAL YEAR TO MONTH
Date-Time Interval (precision ss) No Equivalent Datatype INTERVAL DAY TO SECOND
hh:mm:ss.nnnnnnn TIME EXTRACT(TIMESTAMP)
Boolean
Boolean TRUE / FALSE No Equivalent Data Type BOOLEAN
Binary Data Types
Fixed length binary BINARY No Equivalent Data Type
Variable length binary VARBINARY (8K) RAW (2K) or LONG RAW (8GB)
Available in Trusted Oracle only No Equivalent Data Type MLSLABEL
Up to 128TB VARBINARY(MAX) BLOB (128TB)
16 byte binary value UNIQUEIDENTIFIER RAW(16)
Identifiers
Object Identifier (OID) No Equivalent Data Type REF
Base 64 string heap table row identifier No Equivalent Data Type ROWID
Base 64 string IOT row identifier No Equivalent Data Type UROWID
Polymorphic Data Types
Any named SQL type or transient type No Equivalent Data Type ANYTYPE
An instance of a given type, with data and type description SQL_VARIANT ANYDATA
Values can be SQL or user-defined types No Equivalent Data Type ANYDATASET
Result set pointer (handle) CURSOR REFCURSOR
URI Data Types
Store DBURIRefs No Equivalent Data Type DBURIType
Store URLs to external web pages or to files XML HTTPURIType
Object type for storing XML No Equivalent Data Type URIType
Expose documents in the XML hierarchy No Equivalent Data Type XDBURIType
Spatial Types
  No Equivalent Data Type SDO_GEOMETRY
  No Equivalent Data Type SDO_GEORASTER
  No Equivalent Data Type SDO_TOPO_GEOMETRY
Media Types
Supports the storage and management of audio data No Equivalent Data Type ORDAudio
Supports storage and management of any type of media data No Equivalent Data Type ORDDoc
Supports the storage and management of image data No Equivalent Data Type ORDImage
Representation of image color, texture, & shape No Equivalent Data Type ORDImageSignature
Supports the storage and management of video data No Equivalent Data Type ORDVideo
Characterizes an image by its average color No Equivalent Data Type SI_AverageColor
Encapsulates color values No Equivalent Data Type SI_Color
Sampled raw image relative color frequencies No Equivalent Data Type SI_ColorHistogram
List containing up to four of the image feature No Equivalent Data Type SI_FeatureList
Most significant colors of a rectangle No Equivalent Data Type SI_PositionalColor
Inherent image characteristics (height, width, & format) No Equivalent Data Type SI_Stillimage
Repeating item's coarseness, contrast,
predominant direction and size		 No Equivalent Data Type SI_Texture
DICOM Medical Imaging No Equivalent Data Type DICOM
 
Built-in Operators
Description SQL Server 2008 Oracle 11gR2
Arithmetic Operators
add + +
subtract - -
multiply * *
divide / /
modulus % (modulo) mod
power power function ** (power)
Assignment Operators
value assignment = :=
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 Functionality
bitwise Complement No Equivalent Functionality utl_raw.bit_complement
Comparison Operators
equal to = =
greater than > >
less than < <
greater than or equal to >= >=
not less than/greater than or equal to !< >=
less than or equal to <= <=
not greater than/less than or equal to !> <=
not equal to <> <>
not equal to != !=
not equal to <> =^
not equal to <> ~=
Conditions
ALL ALL
AND AND
ANY ANY
BETWEEN BETWEEN
No Equivalent Functionality DEPTH
No Equivalent Functionality EMPTY
No Equivalent Functionality EQUALS_PATH
EXISTS EXISTS
IN INFINITE
No Equivalent Functionality IS A SET
No Equivalent Functionality IS ANY
No Equivalent Functionality IS NULL
No Equivalent Functionality IS OF ONLY
No Equivalent Functionality IS OF TYPE
No Equivalent Functionality IS PRESENT
LIKE LIKE
No Equivalent Functionality MEMBER OF
No Equivalent Functionality NAN
NOT NOT
OR OR
No Equivalent Functionality PATH
SOME SOME
No Equivalent Functionality SUBMULTISET OF
No Equivalent Functionality UNDER_PATH
Date Operators
add No Equivalent Functionality +
subtract No Equivalent Functionality -
Hierarchical Operators
Limited: Look Up HEIRARCHYID Data Type CONNECT
No Equivalent Functionality CONNECT BY
ISDESCENDANT OF CONNECT BY PRIOR
GETANCESTOR CONNECT BY ROOT
GETLEVEL LEVEL
Recursive Common Table Expression Recursive Common Table Expression
Multiset Operators
No Equivalent Functionality MULTISET
No Equivalent Functionality MULTISET EXCEPT
No Equivalent Functionality MULTISET INTERSECT
No Equivalent Functionality MULTISET UNION
Set
INTERSECT INTERSECT
EXCEPT MINUS
UNION UNION
UNION ALL UNION ALL
String Operators
concatenation + ||
Unary Operators
positive + +
negative - -
 
Built-in Functions
Description SQL Server 2008 Oracle 11gR2
Analytic Functions
Running average AVG AVG
Coefficient of correlation No Equivalent Functionality CORR
Running count by partition COUNT & COUNT_BIG COUNT
Population covariance of a set of pairs No Equivalent Functionality COVAR_COUNT
Sample covariance of a set of pairs No Equivalent Functionality COVAR_SAMP
Cumulative distribution in a group No Equivalent Functionality CUME_DIST
Rank within a group without gaps DENSE_RANK DENSE_RANK
Row ranked first by DENSE RANK No Equivalent Functionality FIRST
First value of an ordered set No Equivalent Functionality FIRST_VALUE
Provides access to a row by offset No Equivalent Functionality LAG
Row ranked last by DENSE RANK No Equivalent Functionality LAST_VALUE
Provides access to a row by offset No Equivalent Functionality 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 Functionality PERCENT_RANK
An inverse distribution function No Equivalent Functionality PERCENTILE_CONT
An inverse distribution function No Equivalent Functionality PERCENTILE_DISC
Rank of a value in a group RANK RANK
Computes ratio of a value to the sum of a set No Equivalent Functionality RATIO_TO_REPORT
Linear regression function No Equivalent Functionality REGR_AVGX
Linear regression function No Equivalent Functionality REGR_AVGY
Linear regression function No Equivalent Functionality REGR_COUNT
Linear regression function No Equivalent Functionality REGR_INTERCEPT
Linear regression function No Equivalent Functionality REGR_R2
Linear regression function No Equivalent Functionality REGR_SLOPE
Linear regression function No Equivalent Functionality REGR_SXX
Linear regression function No Equivalent Functionality REGR_SXY
Linear regression function No Equivalent Functionality REGR_SYY
Assigns row numbers by partition ROW_NUMBER ROW_NUMBER
Sample standard deviation STDEV STDDEV
Square root of the population variance STDVP STDDEV_POP
Cumulative sample standard deviation No Equivalent Functionality STDDEV_SAMP
Cumulative running total SUM SUM
Population variance of a set VARP VAR_POP
Sample variance of a set No Equivalent Functionality VAR_SAMP
Variance of an expression VAR VARIANCE
Collection Functions
Number of elements in a nested table No Equivalent Functionality CARDINALITY
Creates a nested table from selected rows No Equivalent Functionality COLLECT
Creates a nested table of nonempty subsets No Equivalent Functionality POWERMULTISET
As above: Of the specified cardinality No Equivalent Functionality POWERMULTISET_BY_CARDINALITY
Converts a nested table into a unique set No Equivalent Functionality SET
Conversion Functions
ASCII string into the DB character set No Equivalent Functionality ASCIISTR
BFILE from directory + file name FILESTREAM BFILENAME
Bitvector to a number No Equivalent Functionality 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
Convert one character set to another No Equivalent Functionality CONVERT
Unicode string to a string No Equivalent Functionality DECOMPOSE
Char containing hexadecimal digits to raw No Equivalent Functionality HEXTORAW
Number into a Day-to-Second interval No Equivalent Functionality NUMTODSINTERVAL
Number into a Year-to-Month interval No Equivalent Functionality NUMTOYMINTERVAL
A value to its hash No Equivalent Functionality ORA_HASH
Convert RAW to CHAR No Equivalent Functionality RAW_TO_CHAR
Raw into a hexadecimal containing string No Equivalent Functionality RAWTOHEX
Convert RAW to NCHAR No Equivalent Functionality RAW_TO_NCHAR
Raw into a hexadecimal containing 'N' string No Equivalent Functionality RAWTONHEX
Converts RAW to variable length string No Equivalent Functionality RAW_TO_VARCHAR2
Raw into a hexadecimal object REF No Equivalent Functionality 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 Functionality SYS_OP_RAWTONUM
Casts an array as a table No Equivalent Functionality TABLE
An SCN to its timestamp equivalent Not Relevant TIMESTAMPTOSCN
Convert to BINARY_DOUBLE data type CONVERT TO_BINARYDOUBLE
Convert to BINARY_FLOAT data type CONVERT TO_BINARYFLOAT
Convert to CHAR or VARCHAR2 data type STR TO_CHAR
Convert to CLOB data type No Equivalent Functionality TO_CLOB
Convert to DATE data type No Equivalent Functionality TO_DATE
Convert to Day-to-Second Interval data type No Equivalent Functionality TO_DSINTERVAL
LONG or LONG RAW to LOB data type No Equivalent Functionality TO_LOB
Single byte to corresponding multi-byte No Equivalent Functionality TO_MULTI_BYTE
Convert to NCHAR data type No Equivalent Functionality TO_NCHAR
Convert to NCLOB data type No Equivalent Functionality TO_NCLOB
Convert to NUMBER data type No Equivalent Functionality TO_NUMBER
Multi-byte to corresponding single byte No Equivalent Functionality TO_SINGLE_BYTE
Convert to TIMESTAMP data type No Equivalent Functionality TO_TIMESTAMP
To TIMESTAMP WITH TIMEZONE data type No Equivalent Functionality TO_TIMESTAMP_TZ
Convert to Year-to-Month Interval data type No Equivalent Functionality TO_YMINTERVAL
Changes character set No Equivalent Functionality TRANSLATE_USING
Integer value based on Unicode standard UNICODE No Equivalent Functionality
String to UTF8 or UTF16 No Equivalent Functionality UNISTR
Date-Time Functions
Date addition DATEADD +
Date subtraction DATEDIFF -
Add a month No Equivalent Functionality 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 Functionality CURRENT_TIMESTAMP
Current date and time (low second) GETDATE SYSDATE
Current date and time (high precision) No Equivalent Functionality SYSTIMESTAMP
Database's time-zone No Equivalent Functionality DBTIMEZONE
Extract part from date-time or interval No Equivalent Functionality EXTRACT
Alter time zone information No Equivalent Functionality FROM_TZ
Largest of a set of dates No Equivalent Functionality GREATEST
Last day of month No Equivalent Functionality LAST_DAY
Smallest of a set of dates No Equivalent Functionality LEAST
Months between dates No Equivalent Functionality MONTHS_BETWEEN
Time zone conversion No Equivalent Functionality NEW_TIME
First weekday after date No Equivalent Functionality NEXT_DAY
Rounds date to unit specified No Equivalent Functionality ROUND
Current session's time zone No Equivalent Functionality 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 Functionality TRUNC
Time-zone offset No Equivalent Functionality TZ_OFFSET
Data Mining Functions
Cluster ID of the cluster with highest probability No Equivalent Functionality CLUSTER_ID
Degree of confidence of membership of a row No Equivalent Functionality CLUSTER_PROBABILITY
Varray of objects of possible clusters No Equivalent Functionality CLUSTER_SET
Feature ID with highest coefficient value No Equivalent Functionality FEATURE_ID
Varray of objects of all possible features No Equivalent Functionality FEATURE_SET
Value of a given feature No Equivalent Functionality FEATURE_VALUE
Best prediction for the specified model No Equivalent Functionality PREDICTION
Cost measure of a given prediction No Equivalent Functionality PREDICTION_COST
XML with model specific scoring No Equivalent Functionality PREDICTION_DETAILS
Probability for a given prediction No Equivalent Functionality PREDICTION_PROBABILITY
Varray of objects with all possible classes No Equivalent Functionality 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 Functionality 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 Functionality USERENV
Miscellaneous Functions
Null BLOB No Equivalent Functionality EMPTY_BLOB
Null CLOB No Equivalent Functionality EMPTY_CLOB
Length of an NCHAR column No Equivalent Functionality NLS_CHARSET_DECL_LEN
ID of NLS character set No Equivalent Functionality NLS_CHARSET_ID
Name of NLS character set from ID No Equivalent Functionality NLS_CHARSET_NAME
Numeric identifier of current exception code @@ERROR & ERROR_NUMBER SQLCODE
Error message of current exception code No Equivalent Functionality SQLERRM
Hierarchical path of column from root to node No Equivalent Functionality SYS_CONNECT_BY_PATH
16 byte GUID NEWID SYS_GUID
Function that builds descending index values No Equivalent Functionality SYS_OP_DESCEND
Index leaf block ID scan No Equivalent Functionality SYS_OP_LBID
Model Functions
Use left side value on right side calculation No Equivalent Functionality CV
Iterate through data No Equivalent Functionality ITERATE
Iterate a set number of times through data No Equivalent Functionality ITERATE_UNTIL
Current iteration number No Equivalent Functionality ITERATION_NUMBER
Returns expr1 prior to execution No Equivalent Functionality PRESENTNNV
Returns expr1 prior to execution No Equivalent Functionality PRESENTV
Reference prior model values No Equivalent Functionality PREVIOUS
Null Handling Functions
Evaluate one or both operands may be NULL No Equivalent Functionality 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 Functionality NVL2
Map NULL for joins No Equivalent Functionality SYS_OP_MAP_NONNULL
Numeric Handling Functions
Absolute Value ABS ABS
Arc Cosine ACOS ACOS
Arc Sine ASIN ASIN
Arc Tangent of n ATAN ATAN
Arc Tangent1 divided by Arc Tangent2 ATN2 ATAN2
Average AVG AVG
Compute AND operation on bits No Equivalent Functionality BITAND
Smallest integer >= value CEILING CEIL
First non-null value COALESCE COALESCE
Coefficient of correlation No Equivalent Functionality CORR
Pearson's coefficient of correlation No Equivalent Functionality CORR_K
Spearman's Rho correlation coefficient No Equivalent Functionality CORR_S
Cosine COS COS
Hyperbolic Cosine COSH COSH
Cotangent COT No Equivalent Functionality
Number of Values COUNT & COUNT_BIG COUNT
Population covariance No Equivalent Functionality COVAR_POP
Sample covariance No Equivalent Functionality COVAR_SAMP
Cumulative Distribution No Equivalent Functionality CUME_DIST
Degrees DEGREES No Equivalent Functionality
Rank of row in an ordered group No Equivalent Functionality DENSE_RANK
Exponential value EXP EXP
Row ranked first using DENSE_RANK No Equivalent Functionality FIRST
Round down to nearest integer FLOOR FLOOR
Largest of multiple values No Equivalent Functionality GREATEST
Row ranked last using DENSE_RANK No Equivalent Functionality LAST
Smallest of multiple values No Equivalent Functionality LEAST
Natural Logarithm LOG LN
Logarithm, Base 10 LOG10 LOG
Maximum returned value MAX MAX
Middle value of the set No Equivalent Functionality MEDIAN
Minimum returned value MIN MIN
Remainder from modulus using floor use MODULO (%) operator MOD
Returns alternate number if value not a number No Equivalent Functionality NANVL
Percent ranking No Equivalent Functionality PERCENT_RANK
Inverse distribution continuous dist. model No Equivalent Functionality PERCENTILE_CONT
Inverse distribution discrete distribution model No Equivalent Functionality PERCENTILE_DISC
Raise value to exponent power No Equivalent Functionality POWER
Radians from a numeric expression RADIANS No Equivalent Functionality
Random Number RAND dbms_cryto package
Rank in a group No Equivalent Functionality RANK
Linear regression - avg of the independent var. No Equivalent Functionality REGR_AVGX
Linear regression - avg of the independent var. No Equivalent Functionality REGR_AVGY
Linear regression - non-null number pairs No Equivalent Functionality REGR_COUNT
Linear regression - y intercept No Equivalent Functionality REGR_INTERCEPT
Linear regression - coefficient of determination No Equivalent Functionality REGR_R2
Linear regression - slope of the line No Equivalent Functionality REGR_SLOPE
Linear regression - auxiliary function No Equivalent Functionality REGR_SXX
Linear regression - auxiliary function No Equivalent Functionality REGR_SXY
Linear regression - auxiliary function No Equivalent Functionality REGR_SYY
Remainder from modulus using round No Equivalent Functionality REMAINDER
Round to integer place ROUND ROUND
Sign of number SIGN SIGN
Sine SIN SIN
Hyperbolic sine No Equivalent Functionality SINH
Square SQUARE No Equivalent Functionality
Square root SQRT SQRT
Exact probability test for dichotomous variables No Equivalent Functionality STATS_BINOMIAL_TEST
Crosstabulation analysis of nominal variables No Equivalent Functionality STATS_CROSSTAB
Whether two values are significantly different No Equivalent Functionality STATS_F_TEST
Kolmogorov-Smirnov function No Equivalent Functionality STATS_KS_TEST
Value with the greatest frequency No Equivalent Functionality STATS_MODE
Mann Whitney test No Equivalent Functionality STATS_MW_TEST
One-way analysis of variance function No Equivalent Functionality STATS_ONE_WAY_ANOVA
measures significance of a difference of means No Equivalent Functionality STATS_T_TEST
Wilcoxon Signed Ranks test of paired samples No Equivalent Functionality STATS_WSR_TEST
Standard deviation STDEV STDDEV
Square root of the population variance STDEVP STDDEV_POP
Cumulative sample standard deviation No Equivalent Functionality STDDEV_SAMP
Summation SUM SUM
Tangent TAN TAN
Hyperbolic tangent No Equivalent Functionality TANH
Truncates to specified decimal places No Equivalent Functionality TRUNC
Population variance of a set No Equivalent Functionality VAR_POP
Sample variance of a set No Equivalent Functionality VAR_SAMP
Variance of an expression VAR VARIANCE
Construct equi-width histograms No Equivalent Functionality WIDTH_BUCKET
Object Functions
Object reference of an argument No Equivalent Functionality DEREF
Creates a REF to an object row No Equivalent Functionality MAKEREF
Returns a REF of an object instance No Equivalent Functionality REF
Typeid of the most specific type of the operand No Equivalent Functionality SYS_TYPEID
Returns object instance from an object table No Equivalent Functionality VALUE
String Handling Functions
Get the ASCII value of a character ASCII ASCII
Convert ASCII to character CHAR CHR
First non-null value COALESCE COALESCE
Concatenate strings (expression + expression) CONCAT
Converts From One Character Set To Another No Equivalent Functionality CONVERT
Capitalize first letter of each word in string No Equivalent Functionality INITCAP
Starting point of pattern in a string CHARINDEX & PATINDEX INSTR
Starting point in bytes of pattern in a string No Equivalent Functionality INSTRB
Starting point in Unicode of pattern in a string No Equivalent Functionality INSTRC
Starting point in UCS2 of pattern in a string No Equivalent Functionality INSTR2
Starting point in UCS4 of pattern in a string No Equivalent Functionality INSTR4
Length of character string in characters DATALENGTH or LEN LENGTH
Length of character string in bytes No Equivalent Functionality LENGTHB
Convert characters to lower case LOWER LOWER
Pad left side of character string SPACE LPAD
Left trim a string LTRIM LTRIM
NLS initial letter upper case No Equivalent Functionality NLS_INITCAP
NLS lower case No Equivalent Functionality NLS_LOWER
String of bytes used to sort a string No Equivalent Functionality NLSSORT
NLS upper case No Equivalent Functionality NLS_UPPER
Define quote delimiters No Equivalent Functionality QUOTE_DELIMITERS
Regular expression instring No Equivalent Functionality REGEXP_INSTR
Regular expression replace No Equivalent Functionality REGEXP_REPLACE
Regular expression substring No Equivalent Functionality REGEXP_SUBSTR
Replace part of a string with a string STUFF REPLACE
Reverses a character expression REVERSE REVERSE
Pad right side of character string SPACE RPAD
Right trim a string RTRIM RTRIM
Phonetic representation of character string SOUNDEX SOUNDEX
Difference between the SOUNDEX values DIFFERENCE UTL_MATCH built-in Package
Substring in characters LEFT, RIGHT & SUBSTRING SUBSTR
Substring in bytes No Equivalent Functionality SUBSTRB
Substring in Unicode characters No Equivalent Functionality SUBSTRC
Substring in UCS2 No Equivalent Functionality SUBSTR2
Substring in UCS4 No Equivalent Functionality SUBSTR4
Character data converted from numeric data STR TO_CHAR
Translate character string No Equivalent Functionality TRANSLATE
Translate character string using character set No Equivalent Functionality TRANSLATE USING
Change declared type of an expression No Equivalent Functionality TREAT
Left and right trim a string No Equivalent Functionality TRIM
Convert characters to upper case UPPER UPPER
XML Handling Functions
Append value to target XML as a child node No Equivalent Functionality APPENDCHILDXML
Deletes node(s) matched by XPath expression No Equivalent Functionality DELETEXML
Levels in the path specified by UNDER_PATH No Equivalent Functionality DEPTH
Does specified node exist No Equivalent Functionality EXISTSNODE
Returns XMLType instance containing fragment No Equivalent Functionality EXTRACT
Returns a scalar value of the resultant node No Equivalent Functionality EXTRACTVALUE
Inserts value to target XML as a child node No Equivalent Functionality INSERTCHILDXML
Inserts value to target XML before named node No Equivalent Functionality INSERTXMLBEFORE
Relative path that leads to resource No Equivalent Functionality PATH
Generates a URL of datatype DBURIType No Equivalent Functionality SYS_DBURIGEN
Aggregates XML documents or fragments No Equivalent Functionality SYS_XMLAGG
Returns XMLType containing an XML doc. No Equivalent Functionality SYS_XMLGEN
Returns XMLType instance with updated value No Equivalent Functionality UPDATEXML
Returns an aggregated XML document No Equivalent Functionality XMLAGG
Generates a CDATA section No Equivalent Functionality XMLCDATA
Creates XML fragment & expands resulting XML No Equivalent Functionality XMLCOLLATVAL
Generates an XML comment No Equivalent Functionality XMLCOMMENT
Concatenates XML elements No Equivalent Functionality XMLCONCAT
Returns concatenation of XML fragments No Equivalent Functionality XMLFOREST
Parses and generates an XML instance No Equivalent Functionality XMLPARSE
Generates an XML processing instruction No Equivalent Functionality XMLPI
Returns query results as XML QUERY (FLWOR) XMLQUERY
Create new XML value from version & properties No Equivalent Functionality XMLROOT
Returns Varray of top level nodes No Equivalent Functionality XMLSEQUENCE
Creates a string/LOB containing the contents No Equivalent Functionality XMLSERIALIZE
Returns query of XML results as relational data No Equivalent Functionality XMLTABLE
Applies XSLT to XML instance No Equivalent Functionality XMLTRANSFORM
 
Miscellaneous
SQL Server 2008 Oracle 11gR2
Row level locks are a limiting resource Unlimited row level locks
Lock escalation Lock escalation is impossible
Default: Reads block writes, writes block reads Reads do not block writes, writes do not block reads. It is impossible to block a read
All table triggers are AFTER BEFORE and AFTER triggers
All table triggers are statement triggers Triggers can be statement or row level
No Equivalent Functionality Create or Replace syntax for functions, packages, procedures, synonyms, and views
Single Operating System: Windows Windows, Linux, zLinux, UNIX, Sun Solaris, AIX
Triggers commit independent of triggering event Triggers can not commit or rollback independently except as autonomous transactions
No Equivalent Functionality Compiler Pragmas
No Equivalent Functionality Block/Page Recovery from backup
 
Related Topics
A
 
Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2013 Daniel A. Morgan All Rights Reserved